Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Transformation Question

I have source table below:

Col1
Col2
Col3
AType110
AType220
BType230
CType340

I want a table like this

Col1
Type1
Type2
Type3
A1020-
B-30-
C--40

-H

1 Solution

Accepted Solutions
Sokkorn
Master
Master

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

View solution in original post

4 Replies
Sokkorn
Master
Master

Hi Heap,

Look like this one.

Regards,

Sokkorn

Sokkorn
Master
Master

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
Not applicable
Author

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

-H

Anonymous
Not applicable
Author

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

-H