Transformation Question

[Anonymous]

I have source table below:

Col1	Col2	Col3
A	Type1	10
A	Type2	20
B	Type2	30
C	Type3	40

I want a table like this

Col1	Type1	Type2	Type3
A	10	20	-
B	-	30	-
C	-	-	40

-H

[Sokkorn]

Hi Heap,

Look like this one.

Regards,

Sokkorn

[Sokkorn · Accepted solution]

Or you can try this script

[DS]:
LOAD * Inline [
Col1,Col2,Col3
A,Type1,10
A,Type2,20
B,Type2,30
C,Type3,40];


[TMP1]:
GENERIC LOAD * RESIDENT [DS];


[RESULT]:
LOAD DISTINCT Col1 RESIDENT [DS];


DROP TABLE [DS];


FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) AS Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'TMP1.*');
NEXT i


FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN ([RESULT]) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i


drop table TableList;

Regards,

Sokkorn

[Anonymous]

Sokkorn, very helpful post. And similar to my requirement. Unfortunately, it not work for me. I try but got error.

-H

[Anonymous]

Hey, this one work like a charm. Perfectly Sokkorn!!!!

-H