join only with the first row

[rafadvega]

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!!!

[bowesmana · Accepted solution]

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.

[rafadvega]

That is a perfect solution. Thank you very much!