Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis on a key field

Hi,

I want to create a set analysis for three selections saved as bookmarks (Set1, Set2, Set3).

In my example, I have created three tables of data which are linked by field [nr].

My problem is that I can not do set analysis and calculate the number based on the key [nr]. Instead, I have to use the respective [nr1], [nr2] and [nr3] for correct set analys result.

Set1:

1,2,3,4,5,6

Set2:

1,2,3,7,8,9

Set3:

3,4,5,7,10,11

Count({Set2*Set3}  DISTINCT [nr]) = 1 (should be 2)

Count({(Set3*Set2)}  DISTINCT [nr2]) = 2  (this i correct)

Is it not possible to make set analysis and count the number of a key field?

Best regards

Dalle

Enclosed please find my test project.

1 Reply
Not applicable
Author

In these cases, I ususally prefer using a "hub" table with flags to easily count or filter the nr values. The script for this table could be this one:

TotalTable:

Load Distinct

          1 AS nr1,

          nr1 as nr

Resident Data1;

Join

Load Distinct

          1 AS nr2,

          nr2 as nr

Resident Data2;

Join

Load Distinct

          1 AS nr3,

          nr3 as nr

Resident Data3;

Then, you can use the following expression to count your values, not matter about order of conditions:

='Set 1=' & Sum(nr1) & '

Set 2=' & Sum(nr2)  & '

Set 3=' & Sum(nr3)  & '

Set2*Set1=' & Count({<nr2={1}, nr1={1}>} DISTINCT nr) & '

Set2*Set3=' & Count({<nr2={1}, nr3={1}>} DISTINCT nr) & '

Set1*Set3=' & Count({<nr1={1}, nr3={1}>} DISTINCT nr) & '

Set1*Set2*Set3=' & Count({<nr1={1}, nr2={1}, nr3={1}>} DISTINCT nr)

JG