topic Re: Set Analysis And Condition in QlikView

Set Analysis And Condition

erric3210 — Sat, 16 Nov 2024 17:45:23 GMT

Hi All,

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

Re: Set Analysis And Condition

Kushal_Chawda — Wed, 02 Dec 2020 16:39:59 GMT

@erric3210 I don't think set analysis will work here but you can try below expression

=concat( aggr(if(only({<Flag={'Daily'}>}Value)*1e10 + Only({<Flag={'Daily'}>}Units) *1e4 = max(total aggr(only({<Flag={'Daily'}>}Value)*1e10 + Only({<Flag={'Daily'}>}Units) *1e4, Product, Flag)), Product), Product, Flag),',')

Re: Set Analysis And Condition

erric3210 — Wed, 02 Dec 2020 21:03:31 GMT

Hi Kush,

Thanks for the reply. is there any chance to populate result using Set Analysis.

As per my knowledge, we can't use Possible() function inside if condition. I need to use that.

Also, why there is a need to multiply with number.

Regards,.

Eric

Re: Set Analysis And Condition

edwin — Wed, 02 Dec 2020 21:36:28 GMT

try this:

sum({<Units={$(= max({<Value={$(=max({<Flag={"Daily"}>}Value))}>}Units))}>}Units)

it means get product where units is equal to the maximum unit where value is equal to the maximum value. if there is only one product with max value, then the maximum unit would be the one for that product. in your example, if milk and soap both had 10 units they would both be selected

Re: Set Analysis And Condition

edwin — Wed, 02 Dec 2020 21:37:56 GMT

or for your concat:

=concat( distinct {<Units={$(= max({<Value={$(=max({<Flag={"Daily"}>}Value))}>}Units))}>}Product,',')

Re: Set Analysis And Condition

Kushal_Chawda — Wed, 02 Dec 2020 22:07:39 GMT

@erric3210 As I said set analysis won't work here because set evaluates once per chart . So if any chance you are able to find the Max Units for max values but that Max Units will be applied to all the products no matter there is a max value or not.

If you still want to use the set analysis then mix of script solution I can suggest but again there is limitation as you won't get calculation dynamically.

If you could share why you want to use possible then I would try to accommodate in if statement.

Re: Set Analysis And Condition

edwin — Thu, 03 Dec 2020 00:02:02 GMT

it is possible to use set analysis only using the expression i posted

Re: Set Analysis And Condition

erric3210 — Thu, 03 Dec 2020 08:14:59 GMT

HI Edwin,

Thanks for the reply.

Your shared expression won't give desired output because it is bringing one value in the Set expression.

So, your set expression is populating value 10 (Units={'10'}) inside the expression. This will fail if other products have same 10 units. I want result based on both value and units.

Like this:

Concat(DISTINCT {<Units={'250'},Value={'10'}>}Product) or,

Concat(DISTINCT {<Units={'250'}>*<Value={'10'}>}Product)

Regards,

Eric

Re: Set Analysis And Condition

erric3210 — Thu, 03 Dec 2020 12:22:02 GMT

Hi Kush,

Thanks for all the clarification and explaining the theoretical part of Set analysis.

It was very helpful. I'm trying to achieve result by modifying yours & Edwin solution.

Hope that works.

Regards,

Eric

Re: Set Analysis And Condition

erric3210 — Thu, 03 Dec 2020 09:25:08 GMT

Hi @Kushal_Chawda @edwin ,

I modified both of your expressions and I'm achieving the desired output using this:

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

This is considering both scenarios.

Please correct me if I'm wrong here and also let me know whether this is the feasible solution or not.

Regards,

Eric

Re: Set Analysis And Condition

Kushal_Chawda — Thu, 03 Dec 2020 11:15:34 GMT

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

Re: Set Analysis And Condition

edwin — Thu, 03 Dec 2020 12:42:52 GMT

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,',')

Re: Set Analysis And Condition

edwin — Thu, 03 Dec 2020 12:46:42 GMT

@erric3210

Re: Set Analysis And Condition

edwin — Thu, 03 Dec 2020 12:54:13 GMT

@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.

Re: Set Analysis And Condition

erric3210 — Thu, 03 Dec 2020 21:24:41 GMT

Hi @edwin @Kushal_Chawda,

Thanks for correcting the expression.

I really appreciate all your help.

I was great learning for me.

Regards,

Eric