Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ZoeM
Specialist
Specialist

Counting Concatenated Fields

Hello Community.

I am trying to count the number of concatenated fields within the data set and show that figure s a KPI. I am using the below expression:

Count( Concat(DISTINCT Gateways&[Issue ID]) )

But I am getting an error. 

Any help rendered will be appreciated.

Thank you.  

 

 

Labels (4)
1 Solution

Accepted Solutions
ZoeM
Specialist
Specialist
Author

Can you help me understand how you did this? because it is amazing...you have no idea how long I have struggled with this. 

View solution in original post

7 Replies
sunny_talwar

May be you need this

Count(DISTINCT Gateways & [Issue ID])

or this

SubStringCount(Concat(DISTINCT Gateways&[Issue ID], ', '), ', ') + 1
ZoeM
Specialist
Specialist
Author

Thanks for the response Sunny.

The first response works in a straight table, however, I am developing a KPI and it did not work. The main issue I am facing is duplicates and want to focus on distinct items only hence the need to concat. 

I tried the second expressions and I did not get the anticipated result. 

sunny_talwar

What is your dimension in the straight table where this worked? May be you need something like this

Sum(Aggr(Count(DISTINCT Gateways & [Issue ID]), Dimension/s))

 

ZoeM
Specialist
Specialist
Author

So I got the second expression to work. However, it is counting all the results and I need to count only for the Dimension Issue Ctegory = 'Contained'...

=SubStringCount(Concat(DISTINCT Gateways&[Issue ID], ', '), ', ') + 1 

Can I add that clause somewhere within the expression?

sunny_talwar

May be this

=SubStringCount(Concat(DISTINCT {<[Issue Category] = {'Contained'}>} Gateways&[Issue ID], ', '), ', ') + 1 
ZoeM
Specialist
Specialist
Author

Can you help me understand how you did this? because it is amazing...you have no idea how long I have struggled with this. 

sunny_talwar

Glad I was able to help