topic Re: exclusion in set analysis in QlikView

exclusion in set analysis

Mon, 22 Aug 2016 19:33:27 GMT

I have a table which looks like this

Field_A	Field_B
a	A
b	A
c	A
d	A
e	A
f	A
a	B
b	B
c	B

I want the list of Field_A items which are A but not B (which would be here "def")

I was thinking of doing something like :

=concat( {<Field_B={"A"} > - <Field_B={"B"}> } DISTINCT Field_A)

But it doesn't seem to work :

it returns "abcdef" instead of "def"

Could you help me out ?

Thanks !

Re: exclusion in set analysis

sunny_talwar — Mon, 22 Aug 2016 19:35:44 GMT

May be like this:

=Concat({<Field_A = P({<Field_B = {'A'}>}) - P({<Field_B = {'B'}>})>} DISTINCT Field_A)

Re: exclusion in set analysis

Mon, 22 Aug 2016 19:40:19 GMT

That's it thanks a lot for your help !

Re: exclusion in set analysis

sunny_talwar — Mon, 22 Aug 2016 19:41:18 GMT

No problem at all

Re: exclusion in set analysis

vishsaggi — Mon, 22 Aug 2016 19:43:38 GMT

Try this:

Table1:

LOAD * INLINE [

Field_A, Field_B

a, A

b, A

c, A

d, A

e, A

f, A

a, B

b, B

c, B

];

JOIN(Table1)

LOAD DISTINCT Field_A,

Field_B

Resident Table1

WHere Field_B <> 'B';

Re: exclusion in set analysis

maxgro — Mon, 22 Aug 2016 19:44:25 GMT

maybe

=Concat({$ <Field_B={A},Field_A={"=count(distinct Field_B)=1"}>} Field_A)

Re: exclusion in set analysis

Mon, 22 Aug 2016 20:29:37 GMT

Sunny T,

Let's say, I have another Field, Filed_C, and i want to disregard any selection made in that field.

I tried both this formulas, but it doen't to what expected.

Any idea ?

=Concat({<Field_A = P({<Field_B = {'A'} , Field_C= >}) - P({<Field_B = {'B'}> , Field_C= })>} DISTINCT Field_A)

=Concat({< Field_C=, Field_A = P({<Field_B = {'A'} >}) - P({<Field_B = {'B'}> })>} DISTINCT Field_A)

Re: exclusion in set analysis

sunny_talwar — Mon, 22 Aug 2016 20:32:18 GMT

Try this:

=Concat({<Field_A = P({<Field_B = {'A'}>}) - P({<Field_B = {'B'}>}), Field_C=>} DISTINCT Field_A)

Re: exclusion in set analysis

Mon, 22 Aug 2016 21:03:51 GMT

Hey

Unfortunately it doesn't work.

Your last formula has the same result as your first formula when I do a selection on Field_C

Re: exclusion in set analysis

sunny_talwar — Mon, 22 Aug 2016 21:23:29 GMT

How about this:

=Concat({<Field_A = P({<Field_B = {'A'}, Field_C>}) - P({<Field_B = {'B'}, Field_C>}), Field_C=>} DISTINCT Field_A)

Re: exclusion in set analysis

Mon, 22 Aug 2016 21:40:57 GMT

Great that's it ! thank you so much !

Re: exclusion in set analysis

sunny_talwar — Mon, 22 Aug 2016 21:41:54 GMT

Awesome, I got to learn something new today. I did not know about this behavior, but now I do .

Thanks,

Sunny