Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

sarahplymale
Contributor

Set Analysis and Data Model

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:export.png

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)

Capture.PNG.png

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!!

Tags (2)
4 Replies

Re: Set Analysis and Data Model

Hi,

Try with Aggr()

Like

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.

Re: Set Analysis and Data Model

Hi,

Try this,

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
sarahplymale
Contributor

Re: Set Analysis and Data Model


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.

Highlighted

Re: Set Analysis and Data Model

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


 

Great dreamer's dreams never fulfilled, they are always transcended.