Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
kanalavs
Contributor
Contributor
‎2019-01-16 05:01 AM

counting the preson based on rank

Hi Friends,

I have a chart requirement to count the no of persons for each decision label.

Below are the decision labels:

1. CAction-- tagged to Decision rank 1

2. DAction -- tagged to Decision rank 2

3. GAction --tagged to Decision rank 3

4. Warn -- tagged to Decision rank 4

5. Pending -- tagged to Decision rank 5

There are cases where a person tagged to CAction, DAction, Warn, please see below data. But we need to consider the person only one time under the highest priority.

I have written

dimension as :AGGR(FirstSortedValue([DECISION],DECISION_RANK),[PERSONNO])

Expression: count(distinct(PERSONNO)).

The outcome of the script is still considering the persons for multiple decision labels. Please help how to correct the above expression.

I have given sample of data, please find below.

 

 

DECISIONPERSONYear
CAction-2018
CActionXXXXX12018
CActionXXXXX22018
CActionXXXXX32018
CActionXXXXX42018
CActionXXXXX52018
CActionXXXXX62018
CActionXXXXX72018
CActionXXXXX82018
CActionXXXXX92018
CActionXXXXX102018
CActionXXXXX112018
CActionXXXXX122018
CActionXXXXX132018
CActionXXXXX142018
CActionXXXXX152018
CActionXXXXX162018
CActionXXXXX172018
CActionXXXXX182018
CActionXXXXX192018
CActionXXXXX202018
CActionXXXXX212018
CActionXXXXX222018
CActionXXXXX232018
CActionXXXXX242018
CActionXXXXX252018
CActionXXXXX262018
CActionXXXXX272018
CActionXXXXX282018
CActionXXXXX292018
CActionXXXXX302018
CActionXXXXX312018
CActionXXXXX322018
CActionXXXXX332018
CActionXXXXX342018
CActionXXXXX352018
CActionXXXXX362018
CActionXXXXX372018
CActionXXXXX382018
CActionXXXXX392018
CActionXXXXX402018
CActionXXXXX412018
CActionXXXXX422018
CActionXXXXX432018
CActionXXXXX442018
DactionXXXXX42018
DactionXXXXX12018
DactionXXXXX22018
DactionXXXXX52018
DactionXXXXX442018
DactionXXXXX452018
DactionXXXXX462018
DactionXXXXX472018
DactionXXXXX482018
DactionXXXXX492018
DactionXXXXX502018
DactionXXXXX512018
DactionXXXXX522018
DactionXXXXX532018
WarnXXXXX532018
WarnXXXXX542018
WarnXXXXX22018
WarnXXXXX52018
WarnXXXXX572018
WarnXXXXX582018
WarnXXXXX452018
WarnXXXXX462018
WarnXXXXX612018
WarnXXXXX622018
WarnXXXXX632018
WarnXXXXX642018
WarnXXXXX652018
WarnXXXXX662018
WarnXXXXX672018
WarnXXXXX682018
WarnXXXXX692018
WarnXXXXX702018
WarnXXXXX712018
WarnXXXXX722018
WarnXXXXX732018
WarnXXXXX742018
WarnXXXXX752018
WarnXXXXX762018
WarnXXXXX772018
WarnXXXXX782018
WarnXXXXX792018
WarnXXXXX802018
WarnXXXXX812018
WarnXXXXX822018
WarnXXXXX832018
WarnXXXXX842018
WarnXXXXX852018
WarnXXXXX862018
WarnXXXXX872018
WarnXXXXX882018
WarnXXXXX892018
WarnXXXXX902018
WarnXXXXX912018
WarnXXXXX922018
WarnXXXXX932018
WarnXXXXX942018
WarnXXXXX952018
WarnXXXXX962018
WarnXXXXX972018
WarnXXXXX982018
WarnXXXXX992018
WarnXXXXX1002018
WarnXXXXX1012018
WarnXXXXX1022018
WarnXXXXX1032018
WarnXXXXX1042018
WarnXXXXX1052018
WarnXXXXX1062018
WarnXXXXX1072018
WarnXXXXX1082018
WarnXXXXX1092018
WarnXXXXX1102018
WarnXXXXX1112018
WarnXXXXX1122018
WarnXXXXX1132018
WarnXXXXX1142018
WarnXXXXX1152018
WarnXXXXX1162018
WarnXXXXX1172018
WarnXXXXX1182018
WarnXXXXX1192018
WarnXXXXX1202018
WarnXXXXX1212018
WarnXXXXX1222018
WarnXXXXX1232018
GActionXXXXX1232018
GActionXXXXX1242018
GActionXXXXX1252018
GActionXXXXX1262018
GActionXXXXX1152018
GActionXXXXX1162018
GActionXXXXX1172018
GActionXXXXX1182018
GActionXXXXX12018
GActionXXXXX1322018
GActionXXXXX1332018
GActionXXXXX1342018
GActionXXXXX1352018
GActionXXXXX1362018
GActionXXXXX1372018
GActionXXXXX1382018
GActionXXXXX1392018
GActionXXXXX1402018
GActionXXXXX1412018
GActionXXXXX1422018
GActionXXXXX1432018
GActionXXXXX1442018
GActionXXXXX1452018
GActionXXXXX1462018
GActionXXXXX1472018
GActionXXXXX1482018
GActionXXXXX1492018
PendingXXXXX1502018
PendingXXXXX12018
PendingXXXXX1522018
PendingXXXXX1532018
PendingXXXXX1352018
PendingXXXXX1552018
PendingXXXXX1562018
685 Views
0 Likes
1 Reply
pasi_lehtinen
Partner - Contributor III
Partner - Contributor III
‎2019-01-16 05:48 AM

Hello,

 

I was able to solve your issue on the load script. 

TST:
Load 
    pick(match(Decision,'CAction','DAction'),dual('CAction',1),dual('DAction',2)) as Decision,
    Person,
    Year
Inline [
Decision, Person, Year
'CAction', 'XXXXX1', 2018
'CAction', 'XXXXX2', 2018
'CAction', 'XXXXX3', 2018
'CAction', 'XXXXX4', 2018
'CAction', 'XXXXX5', 2018
'CAction', 'XXXXX6', 2018
'CAction', 'XXXXX7', 2018
'CAction', 'XXXXX8', 2018
'CAction', 'XXXXX9', 2018
'DAction', 'XXXXX1', 2018
'DAction', 'XXXXX2', 2018
'DAction', 'XXXXX4', 2018
'DAction', 'XXXXX5', 2018
];

TST2:
noconcatenate Load
    Person,
    Year,
    Max(Decision) as Decision
Resident TST
group by Person, Year;

drop table TST;

After this you are able to simply use following chart expressions:

Dimension: Decision

Measure: count(distinct Person)

 

This will reduce the rows on your data model. In case you need to have all initial rows in your data model for some reason, you can use the following script:

TST:
Load 
    pick(match(Decision,'CAction','DAction'),dual('CAction',1),dual('DAction',2)) as Decision,
    Person,
    Year
Inline [
Decision, Person, Year
'CAction', 'XXXXX1', 2018
'CAction', 'XXXXX2', 2018
'CAction', 'XXXXX3', 2018
'CAction', 'XXXXX4', 2018
'CAction', 'XXXXX5', 2018
'CAction', 'XXXXX6', 2018
'CAction', 'XXXXX7', 2018
'CAction', 'XXXXX8', 2018
'CAction', 'XXXXX9', 2018
'DAction', 'XXXXX1', 2018
'DAction', 'XXXXX2', 2018
'DAction', 'XXXXX4', 2018
'DAction', 'XXXXX5', 2018
];

TST2:
noconcatenate Load
    Person,
    Year,
    Max(Decision) as Decision
Resident TST
group by Person, Year;

left join(TST)
Load
    *,
    1 as HighestPriorityFlag
Resident TST2;

Drop table TST2;

After this your chart expressions should be:

Dimension: Decision

Measure: count(distinct {<HighestPriorityFlag={1}>} Person)

 

- Pasi

673 Views
0 Likes