Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.
Thank you Gysbert,
PERFECT, I tested the ApplyMap solution. Efficient and easy!