topic Set Analysis Exclusion question in QlikView

Set Analysis Exclusion question

jduluc12 — Sat, 16 Nov 2024 01:25:09 GMT

Hi,

My data set looks like this.

[a]:
LOAD * INLINE [
ID, Type
1, S
2, S
3, C
4, S
5, S
6, C
22, S
];

NoConcatenate
[b]:
LOAD * INLINE [
ID, SNN
1, AAA
2, BBB
3, AAA
4, FFF
5, GGG
6, FFF
22, III
];

I want to find all SNN which are connected to Type 'S' but not with Type 'C'.

In this data set, there are 3 such SNN.

I am writing an exclusion set analysis.

=Concat({<Type={'S'}> - <Type={'C'}>}SNN, ',')

and it returns all SNNs.

AAA, BBB, FFF, GGG, III.

What am I doing wrong here?

Re: Set Analysis Exclusion question

ngioux — Mon, 27 Jan 2020 08:21:22 GMT

Hi,

Your formula is an equivalent to filter rows in the table "a" and then looking for corresponding values in the table "b" (so all SNN values linked to Type 'S').

To achieve what you want, I suggest to filter directly on the SNN field, using P() function (see https://help.qlik.com/en-US/sense/September2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetAnalysis/set-modifiers-implicit-field-value-definitions.htm) :

=Concat({$<[SNN] = P({$<[Type] = {"S"}>} [SNN])-P({$<[Type] = {"C"}>} [SNN])>} [SNN], ',')

Hope this helps you.

Best regards,

Nicolas

Re: Set Analysis Exclusion question

jduluc12 — Tue, 28 Jan 2020 04:55:34 GMT

This also worked

=Concat({<SNN=E({<Type={'C'}>})>}SNN, ',')