Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Intersection in set analysis

Hi All,

This is my first post on Qlikview community, I usually find the answers to all the questions I have on Qlikview community. I was using set analysis in qlikview and it wasnt giving me the ouptput that i thought it should. Basically, I have a simple table with name and value fiels as follows.

Name
Value
n11
n12
n13
n23
n24

Now I want to get the intersection and delta for the field values. That is I want the following:

1. Union: Count of unique values that belong to n1 and n2 together (1, 2, 3, 4) =4

2. Exclusion: Count of unique values that belong to only n1 (1, 2)=2

3. Exclusion: Count of unique values that belong to only n2 (4)=1

4. Intersection: Count of unique values that belong ONLY to to both n1 and n2 (3)=1

5. Symmetric difference: Count of unique values that belong to both n1 and n2, but not to both (1, 2, 4)=3

So I uses following set analysis:

1. Count({1<Name={'n1'}> + 1<Name={'n2'}>}DISTINCT Value)

2. Count({1<Name={'n1'}> - 1<Name={'n2'}>}DISTINCT Value)

3. Count({1<Name={'n2'}> - 1<Name={'n1'}>}DISTINCT Value)

4. Count({1<Name={'n1'}> * 1<Name={'n2'}>}DISTINCT Value)

5. Count({1<Name={'n1'}> / 1<Name={'n2'}>}DISTINCT Value)

However, I am unable to get any result except the 1st result. I am getting the following results

1. Count({1<Name={'n1'}> + 1<Name={'n2'}>}DISTINCT Value) --> 4

2. Count({1<Name={'n1'}> - 1<Name={'n2'}>}DISTINCT Value) --> 3

3. Count({1<Name={'n2'}> - 1<Name={'n1'}>}DISTINCT Value) --> 2

4. Count({1<Name={'n1'}> * 1<Name={'n2'}>}DISTINCT Value) --> 0

5. Count({1<Name={'n1'}> / 1<Name={'n2'}>}DISTINCT Value) --> 4

I want to understand why these operations give misleading results, and what can I do to correct this? 

I have attached my qvw file for refernce

Any help is greatly appreciated.

1 Solution

Accepted Solutions
Not applicable

Re: Intersection in set analysis

Hi Buddy,

Find attachment.

I have worked out with your first data set..

Hope it helps..

Karthik

5 Replies
Not applicable

Re: Intersection in set analysis

You need to add the FLAG 0 if the value have duplicates to the table.

Highlighted
Not applicable

Re: Intersection in set analysis

Please find the attched qvw

Not applicable

Re: Intersection in set analysis

Thank you dathu, but in this you are setting the flag by comparing it with the previous value. Its a hack and wont work if the data set is changed. for instance, i changed the data set to add teh following rows:

n2, 5

n2, 6

n1, 4

and it doesnt give the desired result.

I have attached the qvw file after modifying the dataset..

Any other solution?

Not applicable

Re: Intersection in set analysis

Hi Buddy,

Find attachment.

I have worked out with your first data set..

Hope it helps..

Karthik

Not applicable

Re: Intersection in set analysis

Perfect!! Thank u so much Karthik