topic Re: Comparing groups of records within a table in QlikView

Comparing groups of records within a table

Tue, 23 Sep 2014 08:35:54 GMT

Hi,

Please take a look at this sample application. What I did here was to load my main table (Fruits). Then create two separate identical tables from it in the load script. This way I could then have two identical, but disjoint list boxes of all fruits. Here the user can pick a fruit (or group of fruits) from each one of these two list boxes and the table to the right will compare the average count of the selected fruits from each list box.

My question is this. Can one accomplish this functionality without creating two copies of the original table. My real table has millions of rows and this is not an optimum solution.

Re: Comparing groups of records within a table

crusader_ — Tue, 23 Sep 2014 08:41:50 GMT

Hi,

Alternate States is solution for your issue.

Read more here How to Comparative Analysis.

PFA.

Hope this helps you.

Andrei

Re: Comparing groups of records within a table

demoustier — Tue, 23 Sep 2014 08:48:44 GMT

Like in attached File ?

Re: Comparing groups of records within a table

Tue, 23 Sep 2014 09:14:16 GMT

I see now how the Alternate States feature works; however, my requirements are to implement it exactly as in the straight table in my original sample qvw file. Can't seem to be doable with Alternate States.

Re: Comparing groups of records within a table

demoustier — Tue, 23 Sep 2014 09:38:19 GMT

In this case, I have no other solution than yours....sorry

Re: Re: Comparing groups of records within a table

crusader_ — Tue, 23 Sep 2014 10:09:36 GMT

Hi,

Check wether it acceptable for you.

PFA.

Hope this helps.

Andrei

Re: Comparing groups of records within a table

Tue, 23 Sep 2014 16:36:35 GMT

Perfect. Thanks for your help. Can you explain the $* notation in the expression? Does not look like the set analysis syntax I have seen before:

avg({$*A} [Count])

Re: Comparing groups of records within a table

crusader_ — Wed, 24 Sep 2014 06:12:14 GMT

Hi,

It's Alternate State syntax in set analysis.

$*A means Selection and State 'A' - intersection give us correct result.

Hope this helps you.

PS please mark my previous answer (correct or helpful) for future users who will have same issue.

Andrei

Re: Comparing groups of records within a table

Thu, 25 Sep 2014 20:39:25 GMT

i just notice that another non alternate state list is interfering with my two alternate list boxes.If I have a third non-alternate state Fruit list box in the app, it affects what the user selects from the alternate state lists. I tried every syntax I could think or researched, but nothing seems to work. Do you know how to include all elements from this list box in the expression, ie, {<Fruit=>}

Re: Comparing groups of records within a table

crusader_ — Fri, 26 Sep 2014 06:24:35 GMT

Oh, yes... Sorry for this confusion.

I've never faced it before because I haven't used "third" listbox.

To fix this remove $-sign.

avg({A} [Count])

avg({B} [Count])

Regards,

Andrei

Re: Re: Comparing groups of records within a table

Fri, 26 Sep 2014 20:18:15 GMT

Hi,

Yes, I tried that but the problem is this syntax cuts off all other selections from other fields in the app. What I need is to include the alternate state selection plus all other selections in the app minus the third inherited Fruit list box. So I tried the following syntax:

{$*A <Fruit=>}

assuming it accomplishes the above, but it seems like it completely ignores the {A} state and only takes the inherited Fruit list box

So in the modified attached app, if peach is selected from Fruit A box, selecting stores S1 or S3 should not change the Group A value since peach selected in Fruit A belongs to both stores- it should return 8. And if the user selects the S2 store it should not return anything since peach does not belong to that store.