Set Analysis - Exclude and give unique count

[#1]

Hi,

I am facing a challenge when using set analysis to output a straight table (count) chart to find unique counts in particular categories.

Let say we have for example the following data:

Category	Name
A	Allyson Wiltshire
A	Lonnie Spillane
A	Tyrone Thorington
B	Allyson Wiltshire
B	Chandra Saum
B	Javier Weaks
B	Milagros Helling
B	Penelope Magyar
C	Allyson Wiltshire
C	Cody Goodner
C	Kurt Clever
C	Lonnie Spillane
C	Penelope Magyar

From the above data I wish to extract the unique count of each category. For example, category A has only 1 name as the other names can be found in other categories; as for example Lonnie Spillan can be found also in category C. Category B has 3 names, Chandra Saum, Javier Weaks, Milagros Helling. Category C has 2 names Cody Goodner, Kurt Clever; as for example Penelope Magyar can be found also in category B.

In summary the output should be:

A = 1 {Tyrone Thorington}

B = 3 {Chandra Saum, Javier Weaks, Milagros Helling}

C = 2 {Cody Goodner, Kurt Clever}

Is it possible to perform this in set analysis? Attached is a qlikview file so that we can experiment with the data.

Thanks for your help

Chris

[Jason_Michaelides · Accepted solution]

Hi Chris,

See attached.  In the script, first count the amount of times each name occurs.  Then use this counter in your set analysis.

Hope this helps,

Jason

[#3]

Hi Jason,

Thanks a lot for your reply. You really helped me out. Now what I'm after is another step. How can apply set analysis to the other possible category combination? For example in A,B but not C; A and C but not B, A and B and C; etc

Regards

Chris

[Jason_Michaelides]

Not sure I completely follow.  Using the above data, please give a table of your expected results.

[#5]

I managed to do it using aggregated concat on a calculated dimension (see attached). However if you have a lot of data (around 1,000,000 rows) it's proving impossible with a 6GB RAM laptop. Probably I will have to do it in a load script.