topic Re: Intersection in set analysis in QlikView

Intersection in set analysis

Wed, 26 Jun 2013 01:16:08 GMT

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
n1	1
n1	2
n1	3
n2	3
n2	4

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.

Re: Intersection in set analysis

Wed, 26 Jun 2013 02:07:57 GMT

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

Re: Intersection in set analysis

Wed, 26 Jun 2013 02:45:26 GMT

Please find the attched qvw

Re: Intersection in set analysis

Wed, 26 Jun 2013 04:09:30 GMT

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?

Re: Intersection in set analysis

Wed, 26 Jun 2013 05:06:00 GMT

Hi Buddy,

Find attachment.

I have worked out with your first data set..

Hope it helps..

Karthik

Re: Intersection in set analysis

Wed, 26 Jun 2013 05:20:07 GMT

Perfect!! Thank u so much Karthik

Re: Intersection in set analysis

padales1 — Thu, 27 Feb 2020 11:57:10 GMT

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