Customer switch problem

[ebrambilla]

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!

[Gysbert_Wassenaar · Accepted solution]

mapCode:

mapping LOAD

     OLDCODE,

     NEWCOD

FROM ...mysource1...;

FinalCode:

LOAD

     INITIALCODE,

     applymap('mapCode',

           applymap('mapCode',

               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.

[ebrambilla]

Thank you Gysbert,

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