Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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.
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;
Hi Trymbach
Please attach the excel file? THank you.