topic Re: Customer switch problem in QlikView

Customer switch problem

ebrambilla — Tue, 24 Jun 2014 15:41:04 GMT

Hello,

in our ERP SAP R/3 in the customer record we can use a field containing the "previous customer code". When a new customer record is created to substitute an old one (this may happen for different reasons like changes in VAT, Bankrupty etc.) in the new record we put the old customer code in that field.

So, I can have a table like this:

OLDCODE	NEWCOD
A	B
B	C
C	D
D	E
F	G
H	K

Here, means customer record A was replaced by Customer record B, customer record B was replaced by customer record C and so on.

Now, I want to build a new table showing only initial-final relationships; in the last column we will have the final customer:

INITIALCODE	FINALCODE
A	E
B	E
C	E
D	E
F	G
H	K

The number of levels is not known but we could assume is limited to 10.

Anyone has an idea on how to afford the problem?

Thanks!

Re: Customer switch problem

Gysbert_Wassenaar — Tue, 24 Jun 2014 15:51:10 GMT

mapCode:

mapping LOAD

OLDCODE,

NEWCOD

FROM ...mysource1...;

FinalCode:

LOAD

INITIALCODE,

applymap('mapCode',

....nested to 10 levels,

....applymap('mapCode',INITIALCODE) ))))))))) as FINALCODE

FROM ...mysource2...;

Another option you can try is using the Hierachy() function to build the replacement tree and then use the root and the leaf for each code.

Re: Customer switch problem

ebrambilla — Tue, 24 Jun 2014 17:11:33 GMT

Thank you Gysbert,

PERFECT, I tested the ApplyMap solution. Efficient and easy!