Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Buddy,
Find attachment.
I have worked out with your first data set..
Hope it helps..
Karthik
You need to add the FLAG 0 if the value have duplicates to the table.
Please find the attched qvw
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?
Hi Buddy,
Find attachment.
I have worked out with your first data set..
Hope it helps..
Karthik
Perfect!! Thank u so much Karthik
cant access file as i dont have the qlikview on my system. can you please paste the solution here