Count when status is equal specific value only

[Shubham_D]

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

 

[Steven_Haught]

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) 

[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.