Transpose table

[andrew001]

Hi

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

[swuehl · Accepted solution]

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;

[#3]

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,

                              email

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.

[andrew001]

Thanks

interesting solution  !!!