Set Analysis And Condition

[erric3210]

Hi All,

@MayilVahanan @sunny_talwar 

I'm learning Set Analysis and facing one issue in it.

This is the sample Data:

LOAD * INLINE [
Product, Flag, Value, Units
Eggs, Daily, 100, 40
Eggs, Weekly, 1000, 400
Soap, Daily, 250, 10
Soap, Weekly, 2500, 100
Milk, Daily, 250, 8
Milk, Weekly, 2500, 80
Curd, Daily, 140, 20
Curd, Weekly, 1400, 200
];

I need all those products which have Flag='Daily' and Maximim Value. The Maximum value for Flag Daily is '250' and if there are 2 same values
like I've in my Data Set '250' so for such cases I need to show the Product which has Max units.

2 conditions need to be fulfilled.
So, from the above table, the result should be:

Product, Flag, Value, Units
Soap, Daily, 250, 10

because it have max units '10'

I'm trying to achieve the result through Set Intersection.

See this:

Regards,

Eric

[Kushal_Chawda]

@erric3210  expression seems to be working. But you need to test it out in your actual dataset

[edwin]

i must admit you are on the right track and that i should have tested more scenarios.  at the end of the day, set analysis will boil down to just what was inside the last $().  but you need to add the check for Flag as if weekly has value=250 and unit of 10 it will be picked up.:

=CONCAT(DISTINCT {<Flag={'Daily'}, Value={$(=MAX({$<Flag={'Daily'}>}Value))}>*<Flag={'Daily'}, Units={$(= max({<Value={$(=max({<Flag={"Daily"}>}Value))}>}Units))}>}Product,',')

[edwin]

@erric3210 

i must admit you are on the right track and that i should have tested more scenarios.  at the end of the day, set analysis will boil down to just what was inside the last $().  but you need to add the check for Flag as if weekly has value=250 and unit of 10 it will be picked up.:

=CONCAT(DISTINCT {<Flag={'Daily'}, Value={$(=MAX({$<Flag={'Daily'}>}Value))}>*<Flag={'Daily'}, Units={$(= max({<Value={$(=max({<Flag={"Daily"}>}Value))}>}Units))}>}Product,',')

[edwin · Accepted solution]

@erric3210  i went back to your original post and i think the proper way is:

=CONCAT(DISTINCT {<Flag={'Daily'}, Value={$(=MAX({$<Flag={'Daily'}>}Value))}, Units={$(= max({<Value={$(=max({<Flag={"Daily"}>}Value))}>}Units))}>}Product,',')

since separating the modifiers by comma is already an AND operation.  

[erric3210]

Hi @edwin @Kushal_Chawda,

Thanks for correcting the expression.

I really appreciate all your help.

I was great learning for me.

Regards,

Eric