topic Re: Counting Concatenated Fields in QlikView

Counting Concatenated Fields

ZoeM — Sat, 16 Nov 2024 03:45:11 GMT

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.

Re: Counting Concatenated Fields

sunny_talwar — Thu, 25 Apr 2019 15:18:14 GMT

May be you need this

Count(DISTINCT Gateways & [Issue ID])

or this

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

Re: Counting Concatenated Fields

ZoeM — Thu, 25 Apr 2019 15:24:46 GMT

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

sunny_talwar — Thu, 25 Apr 2019 15:29:53 GMT

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))

Re: Counting Concatenated Fields

ZoeM — Thu, 25 Apr 2019 15:34:13 GMT

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

sunny_talwar — Thu, 25 Apr 2019 15:48:49 GMT

May be this

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

Re: Counting Concatenated Fields

ZoeM — Thu, 25 Apr 2019 15:51:26 GMT

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

sunny_talwar — Thu, 25 Apr 2019 15:52:26 GMT

Glad I was able to help