Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
map1:
mapping LOAD productid,
description
FROM
M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx
(ooxml, embedded labels, table is product);
map2:
Mapping
LOAD productid,
discount
// promoDiscount
FROM
M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx
(ooxml, embedded labels, table is discount);
map3:
Mapping
LOAD customerid,
[customer name]
FROM
M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx
(ooxml, embedded labels, table is customer);
tomap:
LOAD orderid,
ApplyMap ('map1','map2',productid) as pid,
ApplyMap ('map3',customerid) as cid,
productid,
customerid,
gross_amount,
orderDate
FROM
(ooxml, embedded labels, table is sales);
hi,
Join the first two tables into one like below
map1:
LOAD productid,
description
FROM
M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx
(ooxml, embedded labels, table is product);
join
LOAD productid,
discount
// promoDiscount
FROM
M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx
(ooxml, embedded labels, table is discount);
Then using apply map to add the field
Regards
Deepak
In your example the MAP1 & MAP2 have the ProductID and if you have pid in your source data you can use Mulitple ApplyMaps statement to get the description & discount.
LOAD orderid, pid ,
ApplyMap ('map1',pid,'xx') as Description,
ApplyMap ('map2',pid,0) as discount,
ApplyMap ('map3',customerid) as cid,
productid,
customerid,
gross_amount,
orderDate
FROM
(ooxml, embedded labels, table is sales);
I am assuming you are looking the multiple fields
If you need to calculate the field based on the multiple apply maps like below:
MAP1:
MAPPING LOAD A, B SOURCE1;
MAP2:
MAPPING LOAD B,C SOURCE2;
In my source I have A field, but I need a C field, then use like below:
MAIN:
LOAD A,
D,
E
ApplyMap('MAP2' , ApplyMap('MAP1',A,'X') , 'x') AS C
FROM SOURCE;
map1:
Mapping
LOAD productid,
description
FROM
M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx
(ooxml, embedded labels, table is product);
join
Mapping
LOAD productid,
discount
// promoDiscount
FROM
M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx
(ooxml, embedded labels, table is discount);
if i write like this even giving error
map3:
Mapping
LOAD customerid,
[customer name]
FROM
M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx
(ooxml, embedded labels, table is customer);
You can use below script...
map1:
mapping LOAD
productid,
description
FROM
M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx
(ooxml, embedded labels, table is product);
map2:
Mapping LOAD
productid,
discount
// promoDiscount
FROM
M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx
(ooxml, embedded labels, table is discount);
map3:
Mapping
LOAD
customerid,
[customer name]
FROM
M:\ONLYQLIKVIEW\DOCUMENTS\MAPPING\map.xlsx
(ooxml, embedded labels, table is customer);
tomap:
LOAD
orderid,
ApplyMap ('map1','productid','Unknown') as description,
ApplyMap ('map2',productid,'Unknown') as discount,
ApplyMap('map3',customerid,'Unknown') as [customer name],
productid,
customerid,
gross_amount,
orderDate
FROM
(ooxml, embedded labels, table is sales);
i did same its giving result but way of script writing is correct or not
There is nothing wrong in applying three ApplyMap in your script.
In fact, it is better to use ApplyMap as the Mapping load Table is not being kept in RAM after script run.
thanks
if your problem solved, please close this thread by choosing correct answer so that other members can use it.
Thanks
i am new to this site pls tell me where is close button