Hi,
I need to join two searchs. For example:
Example 1:
| inputlookup join_example1.csv
country | product | day | stock |
Spain | apples | 10/10/2022 | 25 |
France | apples | 10/10/2022 | 22 |
Spain | grapes | 10/10/2022 | 30 |
France | grapes | 10/10/2022 | 28 |
Spain | apples | 10/10/2021 | 25 |
France | apples | 10/10/2021 | 22 |
Spain | grapes | 10/10/2021 | 30 |
France | grapes | 10/10/2021 | 28 |
Example 2:
| inputlookup join_example2.csv
day | product | requested |
10/10/2022 | apples | 90 |
10/10/2021 | apples | 110 |
10/10/2022 | grapes | 100 |
10/10/2021 | grapes | 110 |
If I join bot searchs:
| inputlookup join_example1.csv
| join product, day
[| inputlookup join_example2.csv]
| table product day country stock requested
The result is:
product | day | country | stock | requested |
apples | 10/10/2022 | Spain | 25 | 90 |
apples | 10/10/2022 | France | 22 | 90 |
grapes | 10/10/2022 | Spain | 30 | 100 |
grapes | 10/10/2022 | France | 28 | 100 |
apples | 10/10/2021 | Spain | 25 | 110 |
apples | 10/10/2021 | France | 22 | 110 |
grapes | 10/10/2021 | Spain | 30 | 110 |
grapes | 10/10/2021 | France | 28 | 110 |
But I need the sub search merges only with the first result like this (only in one country):
product | day | country | stock | requested |
apples | 10/10/2022 | Spain | 25 | 90 |
apples | 10/10/2022 | France | 22 | 0 |
grapes | 10/10/2022 | Spain | 30 | 100 |
grapes | 10/10/2022 | France | 28 | 0 |
apples | 10/10/2021 | Spain | 25 | 110 |
apples | 10/10/2021 | France | 22 | 0 |
grapes | 10/10/2021 | Spain | 30 | 110 |
grapes | 10/10/2021 | France | 28 | 0 |
That is only a example, I need only merge subsearchs results once. Anyone knows a solution for this?
Thanks!!!
You can add this to the end of your example search
| streamstats c by product day
| eval requested=if(c=1,requested,0)
| fields - c
which simply does a count by product and day and then sets requested to 0 if the count value is not 1
Not sure if this will give you a general solution though.
You can add this to the end of your example search
| streamstats c by product day
| eval requested=if(c=1,requested,0)
| fields - c
which simply does a count by product and day and then sets requested to 0 if the count value is not 1
Not sure if this will give you a general solution though.
That is a perfect solution. Thank you very much!