Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
heap_qlik
Contributor

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
Highlighted
Sokkorn
Honored Contributor

Re: Re: Transformation Question

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
Highlighted
Sokkorn
Honored Contributor

Re: Transformation Question

Hi Heap,

Look like this one.

Regards,

Sokkorn

Highlighted
Sokkorn
Honored Contributor

Re: Re: Transformation Question

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

Highlighted
heap_qlik
Contributor

Re: Transformation Question

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

-H

Highlighted
heap_qlik
Contributor

Re: Transformation Question

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

-H