topic Re: Join and spread data in QlikView

Join and spread data

Anonymous — Thu, 14 Dec 2017 12:52:07 GMT

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

Re: Join and spread data

sunny_talwar — Thu, 21 Dec 2017 16:54:10 GMT

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,

Resident Table;

Concatenate(FinalTable)

LOAD Code,

Resident Table

Where Len(Trim(ID)) > 0;

DROP Table Table;

DROP Field Orig_Code;

Re: Join and spread data

maxgro — Thu, 21 Dec 2017 18:15:39 GMT

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;