Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Sokkorn, very helpful post. And similar to my requirement. Unfortunately, it not work for me. I try but got error.
-H
Hey, this one work like a charm. Perfectly Sokkorn!!!!
-H