topic Re: Count of Distinct Values In One Column When Condition In Another Column Is Met in QlikView

Count of Distinct Values In One Column When Condition In Another Column Is Met

Fri, 24 Jul 2015 17:18:04 GMT

Hi,

I have a table that looks like:

I have "Product Id" in a straight table. For each "Product Id" row, I want to display a column that displays a count according to the following logic: Distinct "Partner ID" values for each "Product Id" where "Status Type" = "CON." I think I know how to just do a simple distinct count, but I'm not sure about the proper syntax for the AND Status Type = CON condition.

If you could be detailed about where exactly I need to put this code in the straight table properties, I would really appreciate it (I'm still pretty new to this). Thanks!

-Kenny

Re: Count of Distinct Values In One Column When Condition In Another Column Is Met

marcus_sommer — Fri, 24 Jul 2015 17:25:52 GMT

You could do it with set analysis within the expression like:

count({< [Status Type] = {'CON'}> } distinct [Partner ID])

- Marcus

Re: Count of Distinct Values In One Column When Condition In Another Column Is Met

sunny_talwar — Fri, 24 Jul 2015 17:26:13 GMT

Try this as your expression:

Count(DISTINCT {<[Status Type] = {"CON"}>} [Partner ID])

and dimension Product Id

Re: Count of Distinct Values In One Column When Condition In Another Column Is Met

Fri, 24 Jul 2015 17:31:22 GMT

Thanks Marc/Sunny for the really quick response. I'm sure one of these works perfectly. I'll test on Mon. and be sure to mark the correct answer. Really appreciate the help!

Re: Count of Distinct Values In One Column When Condition In Another Column Is Met

sunny_talwar — Fri, 24 Jul 2015 17:36:00 GMT

No problem Kenny

We both actually gave the same solution, just the place of DISTINCT is different.

Best,

Sunny

Re: Count of Distinct Values In One Column When Condition In Another Column Is Met

abhi1693r — Wed, 26 Jun 2019 15:16:00 GMT

Hello,

I had a similar issue. So I have two columns, PART AND CUBES, with one to many relationship.(one PART can have many CUBES or a PART can have no CUBE).

Scenario: If I select 3 PARTS which have 2 CUBES, wherein 2 PARTS have 2 CUBES and 1 PART has no cube. I want a count of all the CUBES plus any PART which has no CUBES.

So the count should be 2+1=3.

How can I implement this?

@sunny_talwar @marcus_sommer

Re: Count of Distinct Values In One Column When Condition In Another Column Is Met

marcus_sommer — Thu, 27 Jun 2019 07:02:37 GMT

I'm not sure that I understand what your difficulties are but you could also count over multiple fields maybe with something like this:

count(distinct PART&CUBES)

- Marcus