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-mul...
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
