Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

crosstable reversed

Hi

I have the following table:

EntityWeek
hour
A132
A234
A328
A449
B112

Would like to convert (don't ask why) to a table with 53 columns (Entity + 52  weeks)

Entity12
34...
A32342849
B12



Is this possible witout reccursive joins...?

Dror

1 Solution

Accepted Solutions
Sokkorn
Master
Master

Hi Dror,

This load script should work

[Data]:

LOAD * INLINE [

Entity,    Week,    hour

A,    1,    32

A,    2,    34

A,    3,    28

A,    4,    49

B,    1,    12];

[TMP1]:

GENERIC LOAD * RESIDENT [Data];

[RESULT]:

LOAD DISTINCT Entity RESIDENT [Data];

DROP TABLE [Data];

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

11 Replies
jonbrough
Specialist
Specialist

It is possible on the front end using a pivot with Week as the second (pivoted) dimension.

In the script, you could loop through the instances of Weeks assigning them to new fields as defined by a count variable.

Jonathan

kumarnatarajan
Partner
Partner

Hi,

Why you need this this is possible in pivot table.

1. Add dimension Week And Entity.

2. Add expression Sum(hour).

3. Go to presentation -> check then "Always Fully Expanded".

4. then drag Week head to top.

Find the attached file.

Not applicable
Author

I need to handle this in a script. There would be no UI for the target table. It's merely for an export to database table

sivarajs
Specialist II
Specialist II

Use generic keyword before the load statement to get straight table into a cross table

Not applicable
Author

tried the Generic approach. got 53 different tables. Merge them all to one straight table should be memory intens.

sivarajs
Specialist II
Specialist II

kumarnatarajan
Partner
Partner

jeffmartins
Partner
Partner

Hi Dror Svartzman,

See a solution for this problem in the following link:

http://community.qlik.com/message/270947#270947

Hope this helps you.

Regards

Sokkorn
Master
Master

Hi Dror,

This load script should work

[Data]:

LOAD * INLINE [

Entity,    Week,    hour

A,    1,    32

A,    2,    34

A,    3,    28

A,    4,    49

B,    1,    12];

[TMP1]:

GENERIC LOAD * RESIDENT [Data];

[RESULT]:

LOAD DISTINCT Entity RESIDENT [Data];

DROP TABLE [Data];

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