Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
];
Output.