Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

Hi Buddy,

Find attachment.

I have worked out with your first data set..

Hope it helps..

Karthik

View solution in original post

6 Replies
Not applicable
Author

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

Not applicable
Author

Please find the attched qvw

Not applicable
Author

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
Author

Hi Buddy,

Find attachment.

I have worked out with your first data set..

Hope it helps..

Karthik

Not applicable
Author

Perfect!! Thank u so much Karthik

padales1
Contributor
Contributor

cant access file as i dont have the qlikview on my system. can you please paste the solution here