Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

ZoeM
Contributor II

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.  

 

 

1 Solution

Accepted Solutions
Highlighted
ZoeM
Contributor II

Re: Counting Concatenated Fields

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

7 Replies

Re: Counting Concatenated Fields

May be you need this

Count(DISTINCT Gateways & [Issue ID])

or this

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

Re: Counting Concatenated Fields

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. 

Re: Counting Concatenated Fields

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
Contributor II

Re: Counting Concatenated Fields

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?

Re: Counting Concatenated Fields

May be this

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

Re: Counting Concatenated Fields

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

Re: Counting Concatenated Fields

Glad I was able to help