topic Re: Set Analysis and Data Model in QlikView

Set Analysis and Data Model

sarahplymale — Tue, 23 Sep 2014 15:06:09 GMT

I have a question about how set analysis expression evaluate depending on your data model. I have a situation where I have cases and each case has several materials associated with it and each material is either used or unused. I wanted to use set analysis to get the set of cases with certain materials that were also used (the intersection of the set containing just those materials and the set containing only used materials).

The case identifier is linked to the material usage with a key dimension. Like this:

When I use the expression that does the intersection and returns the Key dimension, the intersection works properly.

({<Material = {'A', 'B'}> * <Qty = {'>0'}>} Key)

When I use the expression that does the intersection and returns the Case dimension, the intersection does not work properly and returns additional cases as belonging to the set.

({<Material = {'A', 'B'}> * <Qty = {'>0'}>} Case)

I have attached an example QVW illustrating this example.

Obviously there is something that I don't understand about how set analysis expressions interact with the data model. Any help or explanation is much appreciated!!

Re: Set Analysis and Data Model

PrashantSangle — Tue, 23 Sep 2014 15:14:43 GMT

Hi,

Try with Aggr()

Aggr(only({<Material = {'A', 'B'}> * <Qty = {'>0'}>} Case),Key)

Regards

Re: Set Analysis and Data Model

PrashantSangle — Tue, 23 Sep 2014 15:29:59 GMT

Hi,

Try this,

Aggr(only({<Material = {'A', 'B'}> * <Qty = {'>0'}>} Case),Key,Material)

Regards

Re: Set Analysis and Data Model

sarahplymale — Tue, 23 Sep 2014 15:34:37 GMT

Yep, that works for getting the intersection correctly and returning the Case! Thanks! Can you explain what is happening here? I'm new to QlikView, and I don't uderstand what the aggr() function is doing here.

Re: Set Analysis and Data Model

PrashantSangle — Tue, 23 Sep 2014 15:47:14 GMT

Hi,

Basically it is like simple mathematics.

When you use intersection of two sets it takes all possible combination of result of two sets.

So find the required data we have to use aggregate using Key field and dimension field

Here Key and Material will give you unique data.

By the way I am very bad in explaining .I hope you understand what i want to suggest.

Regards