Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
MVP
MVP

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

Highlighted
Not applicable

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,

Highlighted

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
Highlighted
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

Highlighted
Not applicable

APPLYMAP solution is fine for me

THX a lot!