Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Not applicable

Re: Intersection in set analysis

Hi Buddy,

Find attachment.

I have worked out with your first data set..

Hope it helps..

Karthik

View solution in original post

6 Replies
Highlighted
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

Highlighted
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?

Highlighted
Not applicable

Re: Intersection in set analysis

Hi Buddy,

Find attachment.

I have worked out with your first data set..

Hope it helps..

Karthik

View solution in original post

Highlighted
Not applicable

Re: Intersection in set analysis

Perfect!! Thank u so much Karthik

Highlighted
Contributor
Contributor

Re: Intersection in set analysis

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