topic Re: Group by, compare and count in QlikView

Group by, compare and count

hakandikmen — Sat, 08 Apr 2023 00:15:07 GMT

All keys start with support.

If there is a value other than the support in the group, don't write it in the support area. But if there is a support or support_ex value in it, let it write it in the support area.

1. Data with only suport

2. From Support to DEV

3. From Support to INFO

Thanks..

Re: Group by, compare and count

BrunPierre — Sat, 08 Apr 2023 09:16:33 GMT

Hi, here's one approach using mapping.

UserGroupMap: Mapping LOAD IF(KEY&'/'&USER_GROUP='WEV-70/DEV','WEV-70D',IF(KEY&'/'&USER_GROUP='WEV-70D/INFO','WEV-70I',KEY)) as KEY, USER_GROUP Inline [ KEY,USER_GROUP A-100,SUPPORT(or SUPPORT_EX) ATM-30,DEV WEV-70,DEV WEV-70,INFO ATM-31,INFO ATM-75,INFO ]; Data: LOAD USER_GROUP, KEY, Count(DISTINCT KEY) as Count Group By USER_GROUP, KEY; LOAD KEY, ApplyMap('UserGroupMap',IF(KEY&'/'&USER_GROUP='WEV-70/DEV','WEV-70D',IF(KEY&'/'&USER_GROUP='WEV-70D/INFO','WEV-70I',KEY))) as USER_GROUP Inline [ KEY,USER_GROUP ATM-30,SUPPORT ATM-30,DEV WEV-70,SUPPORT WEV-70,DEV WEV-70,INFO ATM-31,SUPPORT ATM-31,SUPPORT_EX ATM-31,INFO A-100,SUPPORT A-100,SUPPORT_EX ATM-75,SUPPORT ATM-75,SUPPORT_EX ATM-75,INFO ]; EXIT SCRIPT;

Re: Group by, compare and count

Kushal_Chawda — Sat, 08 Apr 2023 21:52:19 GMT

@hakandikmen try with below expression

=count(distinct{<KEY= e({1<USER_GROUP-={'SUPPORT_EX','SUPPORT'}>})>+<USER_GROUP={'DEV','INFO'}>} KEY)

Re: Group by, compare and count

hakandikmen — Sat, 08 Apr 2023 22:09:45 GMT

thanks.

How can I add this to the script side?

Here comes out of the support. ok.

So what should I do if I just want Support to look? only support in group KEY