topic Re: Transpose table in QlikView

Transpose table

andrew001 — Sun, 04 Nov 2012 14:41:09 GMT

from this input table,

ctp,ID_COUNTER,email

012,1,a012_1@d.com

012,2,a012_2@d.com

012,3,a012_3@d.com

014,1,a014_1@d.com

014,2,a014_2@d.com

016,1,a016_@1d.com

018,1,a018_1@d.com

018,2,a018_2@d.com

I'd like to obtain a kind of transpose table like this, for ID_COUNTER = 1,2,3

ctp,email1,email2,email3
012,a012_1@d.com,a012_2@d.com,a012_3@d.com
014,a014_1@d.com,a014_2@d.com,
016,a016_@1d.com,,
018,a018_1@d.com,a018_2@d.com,

thanks

Re: Transpose table

swuehl — Sun, 04 Nov 2012 15:36:29 GMT

You can do it like this:

INPUT:

LOAD * INLINE [

ctp,ID_COUNTER,email

012,1,a012_1@d.com

012,2,a012_2@d.com

012,3,a012_3@d.com

014,1,a014_1@d.com

014,2,a014_2@d.com

016,1,a016_@1d.com

018,1,a018_1@d.com

018,2,a018_2@d.com

019,2,a018_1@d.com

019,3,a018_2@d.com

];

RESULT:

LOAD ctp, email as email1 Resident INPUT where ID_COUNTER =1;

Join LOAD ctp, email as email2 Resident INPUT where ID_COUNTER =2;

Join LOAD ctp, email as email3 Resident INPUT where ID_COUNTER =3;

drop table INPUT;

Re: Transpose table

Sun, 04 Nov 2012 20:47:23 GMT

Hi,

You can use also the Generic Load:

e.g

/*******************************/

INPUT:

LOAD * INLINE [

ctp,ID_COUNTER,email

012,1,a012_1@d.com

012,2,a012_2@d.com

012,3,a012_3@d.com

014,1,a014_1@d.com

014,2,a014_2@d.com

016,1,a016_@1d.com

018,1,a018_1@d.com

018,2,a018_2@d.com

019,2,a018_1@d.com

019,3,a018_2@d.com

];

flag:

Generic LOAD

ctp,

'Email'&ID_COUNTER,

Resident INPUT;

FOR i = 0 to NoOfTables()

TableList:

LOAD TableName($(i)) as Tablename AUTOGENERATE 1

WHERE WildMatch(TableName($(i)), 'flag.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

LET vTable = FieldValue('Tablename', $(i));

LEFT JOIN (INPUT) LOAD * RESIDENT $(vTable);

DROP TABLE $(vTable);

NEXT i

DROP TABLE TableList;

DROP Fields email,ID_COUNTER;

See the attached qvw.

Hope it help,

Regards,

Yigal.

Re: Transpose table

andrew001 — Mon, 05 Nov 2012 16:11:30 GMT

Thanks

interesting solution !!!