Count unique values from a CrossTable() column

andymanu — Wed, 28 Nov 2018 03:01:16 GMT

Hi All,

The question is part of the discussion posted on the below link.

https://community.qlik.com/t5/New-to-Qlik-Community/Calculate-distinct-values-from-rows-based-on-multiple-dimension/m-p/1511997#M6815

I want to calculate the count of unique values from a CrossTable column.

The answer provided by Jontydkpi is correct and works for the attached data set under the above post. However, when I apply the method on my actual data set, it does not give the unique number of count.

Consider the below sample data record for your reference,

STAFF 1	STAFF 2	STAFF 3	STAFF 4
Millar, Patrick	Cooper, David (Assistant 1)	Millar, Patrick (Manager)	Mills, Peter (Assistant Coordinator)

One of the concern I got is the "comma" (",") which separates the first name and the last name.

The expected outcome from the above row should be 3 Staff Members ( cos the STAFF 1 and STAFF 3 referred to the same member) instead of 4. Sometimes a staff member could have only his/her name(filed STAFF 1) or the name followed by the designation(filed STAFF 3). Thus, the retrieving of the unique staff number should consider name on the Field STAFF 1 against the any other field which got the Staff names.

I use the CrossTable() function and the below code to calculate the unique Staff Numbers

SubField([Staff 1], ' ', 1) as [Staff 1],

The above returns an answer of 4 Staff instead of 3.

I may be missing here. But really appreciate, if some one (may be Jontydkpi or Tresesco) could help me to fix this issue.

Thank you in advance.

Kind regards,

Andy

Re: Count unique values from a CrossTable() column

andymanu — Wed, 28 Nov 2018 05:46:38 GMT

Hi All,
I think I got the answer for the above question after playing around the code.
I simply changed the "SubField([Staff 1], ' ', 1) as [Staff 1]," statement to "SubField([Staff 1], ' (', 1) as [Staff 1], by adding a "(" so that the qlik will not consider anything after the "(" opening bracket.
Not sure whether this is an efficient way of doing the task, but it worked.
Thanks.
Kind regards,
Andy

topic Count unique values from a CrossTable() column in QlikView

Count unique values from a CrossTable() column

Re: Count unique values from a CrossTable() column