Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have 2 Tables:
Table1:
ID
Name
Table2:
ID
Country
On doing a left join of table2 with table1
I need to create a composite key with ID, name. Country
Regards
Hi,
Try like this
Temp:
LOAD
*
FROM Table1;
Left Join(Temp)
LOAD
*
FROM Table2;
Data:
LOAD
AutoNumber(ID & '_' & name & '_' & Country) AS Key,
*
RESIDENT Temp;
DROP TABLE Temp;
Hope this helps you.
Regards,
Jagan.
Hi,
Try like this
Temp:
LOAD
*
FROM Table1;
Left Join(Temp)
LOAD
*
FROM Table2;
Data:
LOAD
AutoNumber(ID & '_' & name & '_' & Country) AS Key,
*
RESIDENT Temp;
DROP TABLE Temp;
Hope this helps you.
Regards,
Jagan.
Hi Jagan
Perfomance is very slow if am using Resident
You can do this via some joins:
Table1:
LOAD
ID,
Name
FROM...
Left Join (Table1)
LOAD
ID,
Country
FROM...
Left Join (Table1)
LOAD Distinct ID,
Name,
Country
ID & Name & Country As CompKey
Resident Table1;
Or via a mapping table and preceding load:
MapCountry:
Mapping LOAD
ID
Country
FROM Table2...
Table1:
LOAD *,
ID & Name & Country As CompKey
LOAD
ID,
Name,
ApplyMap('MapCountry', ID) As Country
FROM Table1...
This may be more efficient as there is one less load.
Hi
Try like this
Table1:
Load
ID
Name
From table1;
Join(Table1)
Load
ID
Country
From table2;
Final:
Load *, AutoNumber(ID & '_' & Name & '_' & Country) AS %Key from Table1;
Drop table Table1;
Hi Senthil,
Try like this using Mapping Load
MapCountry:
Mapping LOAD
ID,
Country
FROM Table2;
Data:
LOAD *,
ID & Name & Country As Key;
LOAD
ID,
Name,
ApplyMap('MapCountry', ID) As Country
FROM Table1;
Hi jagan
Nice explanation. Thank you.
Hi jagan mohan,
I am using this script but giving below error.
Please help me. Thanks in advance.
MapCountry:
Mapping LOAD
[ID,Country
1,INDIA
2,Singapore
3,US
4,UK
5,Japan
];
Data:
LOAD *,
ApplyMap('MapCountry', ID) As Country,
ID & Name & Country As Key;
LOAD * inline [
ID,Name
1,A
2,B
3,C
4,D
5,E
6,F
7,G
8,H
9,I
10,J
];
EXIT Script;
Hi
Try like this
MapCountry:
Mapping LOAD
[ID,Country
1,INDIA
2,Singapore
3,US
4,UK
5,Japan
];
Data:
LOAD *, ID & Name & Country As Key;
LOAD *, ApplyMap('MapCountry', ID, Null()) As Country inline [
ID,Name
1,A
2,B
3,C
4,D
5,E
6,F
7,G
8,H
9,I
10,J
];