Count If in Group by Help

[Panv]

Hi

I have a table with IDs and various statuses associated to those IDs. Some statuses are known as "final statuses" but there is no column signifying this.

A final status can either be approved, rejected, or expired.

I want to count how many times each ID goes through a Final Status.

Here is what table currently looks like:

ID	Status	Update Time
123	Pending	1/1/2020
123	Expired	1/30/2020
123	Approved	2/4/2020
456	Pending	1/4/2020
456	Rejected	1/16/2020
789	Pending	1/24/2020

And here is what i need it to look like:

ID	Count of Final Status
123	2
456	1
789

I know i would have to do a group by but confused how to proceed, given that I'm counting for various items in the same field. any help would be appreciated, thanks!

[lironbaram]

hi 

for this the best approach will be to add a new field in your load script 

the field will be added using this expression 

if (match(Status,'approved', 'rejected', 'expired')>0,1,0) as FinalStatusFlag 

then you will use a simple function in your table 

sum(FinalStatusFlag)

but you can also use this expression in the table directly

sum(if (match(Status,'approved', 'rejected', 'expired')>0,1,0))

[usamabinsadiq]

Hi,

you can use set analysis for this like:

Count({$<Status={'Expired','Approved','Rejected'}>} Status)

hope this help.

Regards,

[Brett_Bleess]

You have received two replies, please be sure to close out your thread by using the Accept as Solution button on the reply that helped you with your question.  If you did something different, you can post that and then use the button to mark that.  This provides the posters credit for the help and lets the other Members know what worked for the use case.  Please be sure to return to the thread and close it out if possible, it is greatly appreciated.

Regards,
Brett

[Saravanan_Desingh]

One solution:

tab1:
LOAD ID, Count(If(Match(Status,'Approved', 'Rejected', 'Expired'),ID)) As [Count of Final Status]
Group By ID
;
LOAD * INLINE [
    ID, Status, Update Time
    123, Pending, 1/1/2020
    123, Expired, 1/30/2020
    123, Approved, 2/4/2020
    456, Pending, 1/4/2020
    456, Rejected, 1/16/2020
    789, Pending, 1/24/2020
];