Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to join tables with new customerId...?

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

1.table

OrderIdold_CustomerId
010913-1AAA
010913-2AAB
020913-1AAC
020913-2AAD
030913-1AAE

2.table

old_CustomerIdnew_CustomerId
AAAZZ1
AABZZ2
AACZZ3

I need:

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

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-1ZZ1
010913-2ZZ2
020913-1ZZ3
020913-2-
030913-1-

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

I would map the customer ID:

MAP:

Mapping LOAD

oldID, newID

FROM ...;

TABLE:

LOAD OrderId,

          applymap('MAP', old_CustomerID) as new_CustomerID

FROM ...;

View solution in original post

5 Replies
tresesco
MVP
MVP

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

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
swuehl
MVP
MVP

I would map the customer ID:

MAP:

Mapping LOAD

oldID, newID

FROM ...;

TABLE:

LOAD OrderId,

          applymap('MAP', old_CustomerID) as new_CustomerID

FROM ...;

Not applicable
Author

APPLYMAP solution is fine for me

THX a lot!