Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

Set Analysis Exclusion question

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.

  • BBB
  • GGG
  • III 

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?

 

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Set Analysis Exclusion question

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/SetA...) :

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

 

Hope this helps you.

Best regards,

Nicolas

View solution in original post

2 Replies
Highlighted
Partner
Partner

Re: Set Analysis Exclusion question

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/SetA...) :

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

 

Hope this helps you.

Best regards,

Nicolas

View solution in original post

Highlighted
Creator
Creator

Re: Set Analysis Exclusion question

This also worked

 

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