topic Count when status is equal specific value only in App Development

Count when status is equal specific value only

Shubham_D — Sat, 16 Nov 2024 01:39:44 GMT

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

Re: Count when status is equal specific value only

Steven_Haught — Mon, 17 Aug 2020 15:30:52 GMT

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)

Re: Count when status is equal specific value only

Saravanan_Desingh — Tue, 18 Aug 2020 02:50:03 GMT

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 ];

Re: Count when status is equal specific value only

Saravanan_Desingh — Tue, 18 Aug 2020 02:51:02 GMT

Output.