Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
anat_dtm
Contributor
Contributor

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