Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table the looks like this:
Table1:
Code | ID | Orig_Code |
1 | 100 | |
2 | 200 | |
2 | 300 | |
3 | 1 | |
3 | 2 |
and I would like to translate Orig_Code 1 and 2 to the relevant IDs for Code 3 and create a table like this:
Code | ID |
1 | 100 |
2 | 200 |
2 | 300 |
3 | 100 |
3 | 200 |
3 | 300 |
Try this
Table:
LOAD * INLINE [
Code, ID, Orig_Code
1, 100
2, 200
2, 300
3, , 1
3, , 2
];
FinalTable:
LOAD Orig_Code,
Code
Resident Table
Where Len(Trim(ID)) = 0;
Left Join (FinalTable)
LOAD Code as Orig_Code,
ID
Resident Table;
Concatenate(FinalTable)
LOAD Code,
ID
Resident Table
Where Len(Trim(ID)) > 0;
DROP Table Table;
DROP Field Orig_Code;
Another one
Table:
LOAD * INLINE [
Code, ID, Orig_Code
1, 100
2, 200
2, 300
3, , 1
3, , 2
];
FinalTable:
LOAD Code, ID RESIDENT Table WHERE NOT LEN(TRIM(Orig_Code));
Map:
MAPPING LOAD Orig_Code, Code RESIDENT Table WHERE LEN(TRIM(Orig_Code)) ;
CONCATENATE (FinalTable) LOAD ApplyMap('Map', Code) as Code, ID RESIDENT Table WHERE NOT LEN(TRIM(Orig_Code));
DROP TABLE Table;