Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Join and spread data

Hi,

I have a table the looks like this:

Table1: 

CodeIDOrig_Code
1100
2200
2300
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:

 

CodeID
1100
2200
2300
3100
3200
3300
2 Replies
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
MVP
MVP

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;

1.png