topic Set analysis - Clients with one value but not another in QlikView

Set analysis - Clients with one value but not another

brunosantos — Wed, 25 Nov 2020 16:16:04 GMT

Hello.

I've been trying but until now I haven't been able to get it right.

Also reading the help on Set Analysis ... still confused (newbie on this).

Let's say I have a table of clients and there Languages.

LOAD * INLINE [

CLIENT, LANGUAGE

A, EN

A, DE

A, PT

B, BR

B, PT

B, EN

C, EN

D, DE

];

If I try to find the number of Clients that speak EN.

Set Analysis:

=Count({1<LANGUAGE={'EN'}>}CLIENT)

I get the value 3 - Which is OK (Clients A, B and C speak EN)

But let's say I want the number of Clients that speak EN, but do not speak DE.

Set Analysis I've been trying:

=Count({1<LANGUAGE={'EN'}-{'DE'}>}CLIENT)

Here I also get the value 3, which is not OK. I should receive value 2 (Clients B and C speak EN but not DE - Client A speaks both Languages).

I believe this should be fairly simple (I hope), but I not getting there.

Thanks a lot in advance.

Best Regards,

Bruno.

Re: Set analysis - Clients with one value but not another

Anil_Babu_Samineni — Thu, 18 May 2017 16:24:31 GMT

Even, Client A also speaks EN Language. Why don't you need this?

Re: Set analysis - Clients with one value but not another

RSvebeck — Thu, 18 May 2017 17:39:17 GMT

Try this:

Count({1<LANGUAGE={'EN'},

CLIENT=e({1<LANGUAGE={'DE'}> CLIENT)

>}CLIENT

Re: Set analysis - Clients with one value but not another

neelamsaroha157 — Thu, 18 May 2017 18:03:14 GMT

Try this

Count({$<CLIENT=E({1<LANGUAGE={'DE'}>}CLIENT)* P({1<LANGUAGE={'EN'}>})>}CLIENT)

Re: Set analysis - Clients with one value but not another

Anonymous — Thu, 18 May 2017 18:06:29 GMT

Hi Robert, you forgot something:

=Count({1<LANGUAGE={'EN'},CLIENT=e({1<LANGUAGE={'DE'}>} CLIENT)>}CLIENT)

Re: Set analysis - Clients with one value but not another

trdandamudi — Thu, 18 May 2017 19:32:56 GMT

One of the way is as below:

Re: Set analysis - Clients with one value but not another

brunosantos — Fri, 19 May 2017 08:29:52 GMT

Problem solved. Thanks for your time and help.

Bruno.

Re: Set analysis - Clients with one value but not another

brunosantos — Fri, 19 May 2017 08:33:05 GMT

Thanks Robin Hausdörfer and Robert Svebeck for your answers.

Bruno.