Join and spread data

[Anonymous]

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

[sunny_talwar]

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;

[maxgro]

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;