Logical AND in Value-Selection on Dashboard

[#1]

Hi QlikView-Community,

I have a , hopefully, simple question. Just for testing reasons, I have threee small data sources. All data sources include the names of a given set of clients. In addition, every data source represents one single system, where the clients are listed in.

Example:

DataSource 1:

Client	DataSource
Client 1	A
Client 2	A
Client 3	A

DataSource 2:

Client	Data Source
Client 1	B
Client 3	B
Client 4	B

DataSource 3:

Client	Data Source
Client 4	C
Client 5	C

What I would like to know is, which of the clients is listed, for example, ONLY in DataSource A AND Data Source B (and NOT in Data Source C). In this example the result would be Client1 and Client 3.

When adding the fields in QlikView and selecting  Data Source A and Data Source B the result is Client 1, 2,3, 4 as QlikView works with a Logical OR - so it lists all clients that are in Data Source A or in Data Source B

How can this be avoided?

Thanks in advance,

Philip

[rajeshvaswani77]

At a very high level, you could use inner join, exists.

thanks,

Rajesh Vaswani

[trappersw]

Hello Philip,

I forced a solution in attatchement, but I suspect you want it more flexible. If so, please elaborate on your problem.

Regards,

Wouter

temp_List:

LOAD Client,

     [Data Source],

     1 as [Data Source Number]

FROM

DataSource1.xls

(biff, embedded labels, table is [Sheet1$]);

temp1_List:

concatenate LOAD Client,

     [Data Source],

     2 as [Data Source Number]

FROM

DataSource2.xls

(biff, embedded labels, table is [Sheet1$]);

temp2_List:

concatenate LOAD Client, 

     [Data Source],

     3 as [Data Source Number]

FROM

DataSource3.xls

(biff, embedded labels, table is [Sheet1$]);

temp3_List:

load

  Client,

  if(count([Data Source])=2,1,0) as Counter

resident temp_List

where [Data Source Number] =  1 or [Data Source Number] = 2

group by Client;

drop table temp_List;

List:

load

  Client

resident temp3_List

where Counter = 1;

drop table temp3_List;

[Gysbert_Wassenaar]

See attached qvw

[trappersw]

Much more elegant indeed