Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Table A | |
Ticket No | Customer Name |
123 | Shankar |
121 | Arun |
31231 | Vijay |
34535 | Sandy |
3534 | James |
Table B | |
Customer | City |
Shankar | Chennai |
Arun | Madurai |
Vijay | Singapore |
Sandy | Belgium |
James | USA |
I want to map the city in Table B to the Customer Names in Table A.
1. I have tried renaming the Customer in Table B as Customer Name to create a sync but the result is not accurate.
Is there a way to create a lookup or applymap or any better solution? Please suggest.
Try this, You just had the word MAPPING missing from your load of Table B, and you must load the mapping table BEFORE you use the applymap command.
TableB:
MAPPING LOAD
Customer,
City
FROM
(ooxml, embedded labels, table is Sheet1);
TableA:
LOAD [Ticket No],
[Customer Name],
ApplyMap('TableB',[Customer Name],'Not Specified') as City
FROM
(ooxml, embedded labels, table is Sheet1);
yes you can load
a mapping table with
AppAdd:
Customer Name CityS
hankar Chennai
Arun Madurai
Vijay Singapore
Sandy BelgiumJ
ames USA
Hi,
Try this :
TableB:
Mapping load
Customer as [Customer Name],
City;
// SQL SELECT * From Table B; //
TableA:
load
[Ticket No],
[Customer Name],
APPLYMAP('TableB',City,null) as City;
// SQL SELECT * From Table A; //
[Table A]:
load * inline [
Ticket No, Customer Name
123, Shankar
121, Arun
31231, Vijay
34535, Sandy
3534, James
];
[Table B]:
load Customer as [Customer Name], City
inline [
Customer, City
Shankar, Chennai
Arun, Madurai
Vijay, Singapore
Sandy, Belgium
James, USA
];
Use a mapping table like this. You will need to use your correct field names and table names.
The mapping table is deleted at the end of the load process so you will just have the Data table containing 3 fields.
City_Map:
mapping load
Customer, City
from TableB ;
Data:
load
TicketNo,
CustomerName,
applymap('City_Map', CustomerName, 'Not Specified') as City
from TableA
Hi,
Please see the attachment..Hope this will help you..
@ Colin,
applymap('City_Map', CustomerName, 'Not Specified') as City
We are mapping CustomerName from Data to City_Map table but to which column? Why we are specifying 'Not Specified'?
Hi Massimo,
I tried this and getting a result like below...
Ticket No, Customer Name City
123, Shankar Chennai
121, Arun Madurai
31231, Vijay Belgium
34535, Sandy Singapore
3534, James USA
123, Shankar
121, Arun
31231, Vijay
34535, Sandy
3534, James
I am getting one entry with City names joined and another entry with same ticket no and Customer Name without city. Please help.
Apply map does not use the field names of the mapping table, the columns can have any name.
It compares the specified field to the first matching value in column1 of the mapping table, and returns the value in column2, of no match is found, then the 3rd value of the applymap statement is executed.
In my example if there is no match, the text 'Not Specified' is returned
Thanks Colin. I am getting blank for all rows.. I am getting -