Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

Hi,

I have a table that looks like:

Table Sample.png

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

1 Solution

Accepted Solutions
Highlighted

Try this as your expression:

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

and dimension Product Id

View solution in original post

6 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

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

- Marcus

Highlighted

Try this as your expression:

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

and dimension Product Id

View solution in original post

Highlighted
Not applicable

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!

Highlighted

No problem Kenny

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

Best,

Sunny

Highlighted
Contributor II
Contributor II

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 

Highlighted
MVP & Luminary
MVP & Luminary

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