Create a field in the data model that allows selecting excluded values

[#1]

Hi all,

Hope you can support me with the following:

I have four input tables (from Excel) with the client base and specific flags (Type, Region & Importance).

 

Now I am trying to select all the clients, which meet following criteria:

• Client type is not VIP
• Client is not from EUROPE
• Importance is not High

The result should be Company E, Company F and Company J.

As you see, first table contains all clients from the database. But only few of them are included in the other 3 tables. Thus tables 2, 3 and 4 have missing values for some clients.

As I understand possible approach could be to create new fields in the script and add basically a flag.

Something like this:

Table2:
LOAD [Client_ID],
[Type]
FROM
Table2.xlsx
(ooxml, embedded labels, table is type);

Table1:
LOAD [Client_ID],
Name,
If( Exists([Client_ID]), Dual('VIP', True()), Dual('No', False()) ) as [Client type]
FROM
Table1.xlsx
(ooxml, embedded labels, table is Base);

But it seems to work only for one table. Do you know, how can I include remaining two tables as well? Shoul I follow a different approach?

Many thanks!

Best Regards

Dimitri

[#2]

Try link below with Mapping tables;

MAP_VIP:

Mapping LOAD ClientID, Type From Type.xls;

MAP_Region:

Mapping LOAD ClientID, Region From Region.xls;

MAP_Imp:

Mapping LOAD ClientID, Importance From Importance.xls;

Table:

LOAD

     ClientID ,

     Name,

     ApplyMap('MAP_VIP',ClientID,'Not Vip') AS Type,

     ApplyMap('MAP_Region',ClientID,'Non Europe') AS Region,

     ApplyMap('MAP_Imp',ClientID,'not High') AS Importance

From Source.

[maxgro]

T1:

load

rowno() as Client_ID,

'Company ' & chr(ord('A')-1 + rowno()) as Name

AutoGenerate 10;

T2:

load * inline [

Client_ID,Type

1,VIP

3,VIP

7,VIP

8,VIP

];

tmp: load Client_ID as C Resident T2;

Concatenate (T2)

load Client_ID, 'NOVIP' as Type resident T1 where not exists(C, Client_ID);

DROP Table tmp;

T3:

load * inline [

Client_ID,Region

1,EUROPE

2,EUROPE

4,EUROPE

];

tmp: load Client_ID as C Resident T3;

Concatenate (T3)

load Client_ID, 'NO EUROPE' as Region resident T1 where not exists(C, Client_ID);

DROP Table tmp;

T4:

load * inline [

Client_ID,Importance

3,High

4,High

9,High

];

tmp: load Client_ID as C Resident T4;

Concatenate (T4)

load Client_ID, 'NO High' as Importance resident T1 where not exists(C, Client_ID);

DROP Table tmp;

[qlikviewwizard]

Hi Trymbach

Please attach the excel file? THank you.