Adding a column to a resident table

[#1]

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)

[jyothish8807]

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

[tresesco]

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;

[Vegar]

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

[ramasaisaksoft]

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 .

[jonathandienst]

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