Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can do it like this:
INPUT:
LOAD * INLINE [
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;
View solution in original post
Hi,
You can use also the Generic Load:
e.g
/*******************************/
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);
DROP TABLE TableList;
DROP Fields email,ID_COUNTER;
See the attached qvw.
Hope it help,
Regards,
Yigal.
Thanks
interesting solution !!!
