topic Count If in Group by Help in QlikView

Count If in Group by Help

Panv — Wed, 08 Apr 2020 03:55:31 GMT

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!

Re: Count If in Group by Help

lironbaram — Wed, 08 Apr 2020 05:13:58 GMT

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))

Re: Count If in Group by Help

usamabinsadiq — Wed, 08 Apr 2020 11:47:58 GMT

Hi,

you can use set analysis for this like:

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

hope this help.

Regards,

Re: Count If in Group by Help

Brett_Bleess — Wed, 29 Apr 2020 15:45:48 GMT

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

Re: Count If in Group by Help

Saravanan_Desingh — Fri, 01 May 2020 00:48:05 GMT

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