topic Re: Help on set analysis Qlik Sense in App Development

Help on set analysis Qlik Sense

danimelo1 — Tue, 05 Dec 2017 10:44:34 GMT

Hi experts,

Giving this table:

I need to show only person id, who have a action different of U5 with the folowing list of Action Reason reason: U2, Z1.

So in this case, it should exclude Person ID: 3048, 6037, 8311, 11530.

I'm trying this but it doesn`t work:

=if([Action]='U5'] and [Action Reason]<>'U2' or [Action Reason]<>'Z1',[Person ID])

What am I missing?

Regards,

Daniel M

Re: Help on set analysis Qlik Sense

Ivan_Bozov — Tue, 05 Dec 2017 11:12:02 GMT

Hi, try this:

Only({<Action-={'U5'}, [Action Reason]={'U2','Z1'}>}PersonID)

Re: Help on set analysis Qlik Sense

danimelo1 — Tue, 05 Dec 2017 11:27:27 GMT

It gives me an error of Invalid dimension. I'm trying to present it in a straight table

Re: Help on set analysis Qlik Sense

Ivan_Bozov — Tue, 05 Dec 2017 11:38:26 GMT

This is a measure.

Edit: I have misread your requirement. So you are trying to exclude the combinations U5+Z1 and U5+U2?

Create the following line in the script.

IF(Action='U5' AND MATCH([Action Reason],'Z1','U2'), 'Exclude','Include') AS Flag

Then your script will look something like this:

Load
    PersonID,
    Action,
    [Action Reason],
    IF(Action='U5' AND MATCH([Action Reason],'Z1','U2'), 'Exclude','Include') AS Flag
Inline [
PersonID,Action,Action Reason
3048,U5,Z1
4433,U9,U2
11530,U5,U2
11135,U5,U1
]
(Delimiter is ',')

In the table use the following measure:

Only({<Flag={'Include'}>}PersonID)

Re: Help on set analysis Qlik Sense

sunny_talwar — Tue, 05 Dec 2017 12:11:38 GMT

Try this

=If([Action] = 'U5' and Match([Action Reason], 'U2', 'Z1'), [Person ID])

Re: Help on set analysis Qlik Sense

danimelo1 — Tue, 05 Dec 2017 15:19:17 GMT

Genius!!

Thank you Sunny, I was expecting if you some day replied this answer. You Rocks!