I searched and saw someone mentioned Splunk could do cartesian product in this way. I'll do more research, thanks for your solution again. ... View more

There is no common field between these two queries. The first one (dbxquery) has start_date/end_date, which are used to check the date range only in the second query (lookup.csv) with its date field. My solution is try to get the cartesian product first (max=0 for this purpose), for each row in the first query, it should return something below: id, start_date, end_date,  date, amount 1, 2020-01-01, 2020-01-04, 2020-01-01, 10 1, 2020-01-01, 2020-01-04, 2020-01-02, 20 1, 2020-01-01, 2020-01-04, 2020-01-03, 10 1, 2020-01-01, 2020-01-04, 2020-01-04, 10 1, 2020-01-01, 2020-01-04, 2020-01-05, 20 ...... Then use the date comparison check: date >= start_date AND date <= stop_date to filter out those unmatched rows in the lookup.csv, after that we get below for the first row in the first query: 1, 2020-01-01, 2020-01-04, 2020-01-01, 10 1, 2020-01-01, 2020-01-04, 2020-01-02, 20 1, 2020-01-01, 2020-01-04, 2020-01-03, 10 1, 2020-01-01, 2020-01-04, 2020-01-04, 10 Aggregate the above rows by sum, we get: 1, 2020-01-01, 2020-01-04, 50 Do you see anything not right? Thanks again! ... View more

Thanks a lot! Below is my solution, but there are some minor differences of the total. not sure which part might be wrong. | dbxquery connection=my_connection query="SELECT * FROM my_table" | join max=0 [| inputlookup lookup.csv | fields *] | where date >= start_date and date <= end_date | stats sum(amount) as total by id, start_date, end_date ... View more