topic Re: Set inside a set in QlikView

Set inside a set

ferha_jafri — Wed, 16 Aug 2017 05:27:29 GMT

Hello Everyone,

I need help in writing an expression in which I want exclude POL1,POL3,POL4 for location 3704 in a way that I should get the remaining one. Eg total mailpiece count is 9 and exclusion is 2 so and should be 7. Attaching the sample.

Thanks in advance

Ferha Sharma

Re: Set inside a set

tresB — Wed, 16 Aug 2017 05:44:42 GMT

Which expression are you working on?

Re: Set inside a set

ferha_jafri — Wed, 16 Aug 2017 05:51:26 GMT

=sum({<Omc_Location_Id={$(=Concat({<Pre_Adv_Customer-={$(vCustomer_Not)}>}Location_Not,','))}>}Mailpiece_Count)

I want to write an expression in a way that it give me a result of 7 as 9 -2 =7

Re: Set inside a set

antoniotiman — Wed, 16 Aug 2017 06:10:29 GMT

Hi Ferha,

maybe

=sum({<Location_Not={'3704'},Pre_Adv_Customer={'POL1*','POL3*','POL4*'}>} Mailpiece_Count)

Regards,

Antonio

Re: Set inside a set

Anil_Babu_Samineni — Wed, 16 Aug 2017 06:16:50 GMT

Where we need to look? I mean which sheet? Which object?

Re: Set inside a set

ferha_jafri — Wed, 16 Aug 2017 06:18:23 GMT

sheet 2 and object table , expression is mentioned above.

Re: Set inside a set

tresB — Wed, 16 Aug 2017 06:28:22 GMT

If you are only looking for output '7' then try like:

=Sum({1}Mailpiece_Count) -

Sum({<Pre_Adv_Customer-={"$(=Only({<Omc_Location_Id={3704}>}Notcus))"}>}Mailpiece_Count)

Note: The logic you are assuming to exclude the field values and get the output is not correct, because the values are not mutually exclusive against other field values.

Re: Set inside a set

ferha_jafri — Wed, 16 Aug 2017 07:20:33 GMT

yes that is correct but my condition is I want to get 7 without implementing a condition as sum(mailpiececount)-sum({<Location_Not={'3704'},Pre_Adv_Customer={'POL1*','POL3*','POL4*'}>} Mailpiece_Count) as 9-2=7 ,

I want to write a expression as one to get the result.

Re: Set inside a set

antoniotiman — Wed, 16 Aug 2017 07:55:23 GMT

Can You explain why result is 7 ?

Edit : my expression is

=sum({<Location_Not={'3704'},Pre_Adv_Customer-={'POL1*','POL3*','POL4*'}>} Mailpiece_Count)

Re: Set inside a set

ferha_jafri — Wed, 16 Aug 2017 08:21:36 GMT

because I want to exclude it , so the sum of mailpiececount is 9 and the condition will give 2 so the ans is 7

Re: Set inside a set

ferha_jafri — Wed, 16 Aug 2017 11:18:09 GMT

Thanks Tresesco , I understood the expression but did not understand the reason which you gave, it would be helpful if you elaborate it more ,as why the condition cannot be implemented as I want it to implement.

Re: Set inside a set

tresB — Wed, 16 Aug 2017 11:37:04 GMT

If you exclude POL1,POL3,POL4 and sum it, it actually sums for rest (selected in screenshot 1) of the Pre_Adv_Customer which is like in case 1 in screenshot that doesn't give you 7. If you fix the selection of Omc_location_id to '3704' then the sum becomes 1( screen shot 2), because only one from rest members of Pre_Adv_Customer is relevant to it (refer 2 and 3).

Hope this helps.