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: 
Shubham_D
Partner - Creator
Partner - Creator

Count when status is equal specific value only

Hi,

If I have data like below and I have bucket of Status 1 , Status 2 and Both 

Now I want if ID is equal to that Status only, so in below I am expecting 

Count(distinct IDs) for "Status 1" 0 

Count(distinct  IDs) for "Status 2" 0 

Count(distinct  IDs) for  "Both" should be 1

 

1234.png

 
Labels (1)
3 Replies
Steven_Haught
Creator III
Creator III

I assume that you want those counts in a KPI in the app? 

I created some dummy fields to help with this. 

In your first section that has the original data you could add this preceding load to create two dummy fields: 

Load
*,
If(Status='Status 1', 1) as Status1,
If(Status='Status 2', 1) as Status2,

;

 

Then in a new section you could add the following:

Finalchange:

Load
*,
(Stat_temp+Status2) as BothStatus
;

Load
*,


If(Len(Trim(Status1)), Status1, Peek(Stat_temp)) AS Stat_temp
;

Load
*

Resident OriginalTableName
order by ID, Status asc;
Drop Table OriginalTableName;

 

KPI in App would use set analysis to count the BothStatus  field:

Count(Distinct {<BothStatus={2}>}IDstatus) 

Steven_Haught_1-1597678123234.png

 

Saravanan_Desingh

One solution is.

tab1:
LOAD * INLINE [
    IDs, Status
    1, Status 1
    1, Status 2
];

Left Join(tab1)
LOAD IDs, Concat(Status,'&') As tmpKey
Resident tab1
Group By IDs;

LOAD * INLINE [
    tmpKey, Key
    Status 1, Status 1
    Status 2, Status 2
    Status 1&Status 2, Both
];
Saravanan_Desingh

Output.

commQV70.PNG