Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table loaded like so:
myTable:
load
id,field1
From...
then I left join it with another table such as
left join (myTable) load
id,field2
from ...
What I need now is to add a new key field to myTable :
field1&field2
adding this to the left join statement above fails. What is the best solution to do this for large data files (I would like to avoid re-loading a new table from resident and dropping the original table)
Hi Denis,
Try like this:
myTable:
load
id,field1
From...
left join (myTable)
load
id,field2
from ...
Tab2:
Load
field1&field2 as New field
resident myTable;
Regards
KC
Try like:
myTable:
load
id,field1
From...;
left join (myTable) load
id,field2
from ...;
DesiredTable:
Load
*,
field1&field2 as NewField
Resident myTable;
Drop Table myTable;
I world go with resesco's solution. I would do a change to the key field
field1&field2 as key
I would make sure that the unique field1 and field2 combination gets an unique key.
Imagine the following
Field1 = 1 and Field2 = 12. --> Key = 112
Field1 = 11 and Field2 = 2 --> Key = 112
I tend to put pipe between each field in a composite key to avoid this problem.
field1 & '|' &field2 as key
I even do some kind of autonumber on the field to avoid long strings.
E.g. autonumber(field1 & '|' &field2) as key
Hi Dennis,
why don't you try like below
Load *,
filed1 &'_'& field2 as field3;
Actually we generally used this type of scenario for removing the Synthetic keys in table
i hope it will be helpful to u .
Hi
If the you are only getting one or two fields from table2, then use a mapping to add it to table1, like this:
MapField2:
Mapping LOAD id, field2 from ...
myTable:
load
id,
field1,
ApplyMap('MapField2', id) As field2,
field1 & ApplyMap('MapField2', id) As field3
From...
(If there is more than one field, you can concatenate them into a single mapping or build multiple mapping tables; testing would indicate which is better in your case.)
HTH
Jonathan