3 Column Comparison Table Across 3 Datastreams

[erikg]

Hello,

I think I might know the answer but let me know if I am wrong. I have 3 datastream datasets. They contain similar fields and I want to build a table that will compare all 3 based on certain criteria.
Like this:

I want to use this table for insights into Product1 (using the example above) matches each area or dataset.

I am assuming this is essentially JOINs in Elastic which is currently limited to lookup indices not datastreams.

I am thinking the only way to go about doing this using Pandas (outside of elastic) to merge these datastream and then write to a new index?

[Tortoise]

Hello @erikg

Can we use ES|QL , if we consider datastreams as product-a,product-b,product-c & all has similar columns :

ES|QL query

FROM product*
| EVAL 
    range_10_20 = (value >= 10 AND value < 20),
    range_20_30 = (value >= 20 AND value < 30),
    range_30_40 = (value >= 30 AND value < 40)
| STATS 
    max(range_10_20),
    max(range_20_30),
    max(range_30_40)
    BY product
| SORT product

Output :

Thanks!!