how to join tables with new customerId...?

[#1]

how can I join the tables to get result like below:

1.table

OrderId	old_CustomerId
010913-1	AAA
010913-2	AAB
020913-1	AAC
020913-2	AAD
030913-1	AAE

2.table

old_CustomerId	new_CustomerId
AAA	ZZ1
AAB	ZZ2
AAC	ZZ3

I need:

OrderId	new_CustomerId
010913-1	ZZ1
010913-2	ZZ2
020913-1	ZZ3
020913-2	AAD
030913-1	AAE

The result table should have the old_CustomerId when no new_CustomerId exists.

When I do a 'left join' my result table look like this:

OrderId	new_CustomerId
010913-1	ZZ1
010913-2	ZZ2
020913-1	ZZ3
020913-2	-
030913-1	-

How can I push the the old_CustomerId into my result table when no new_CustomerId exists???

[tresesco]

Do an inner join, and then use a concatenation with not exists().

[#3]

Hi,

You just need to reload the table and check if the new_CustomerId is null:

FinalTable:

Noconcatenate LOAD*,

if(IsNull(new_CustomerId), Old_CustomerId, new_CustomerId) as Correcto_CustomerId

Resident YourTable;

Bye,

[jonathandienst]

Hi

I would use ApplyMap, as that will keep the old value if there is no mapping. Create a mapping table BEFORE your main load:

MapOldNewCustomerId:

Mapping Load

     old_CustomerId,

     new_CustomerId

From .....

Then when loading the orders:

Load

     OrderId,

     ApplyMap('MapOldNewCustomerId', old_CustomerId) As new_CustomerId,

     ....

From ....

Hope that helps

Jonathan

[swuehl · Accepted solution]

I would map the customer ID:

MAP:

Mapping LOAD

oldID, newID

FROM ...;

TABLE:

LOAD OrderId,

          applymap('MAP', old_CustomerID) as new_CustomerID

FROM ...;

[#6]

APPLYMAP solution is fine for me

THX a lot!