crosstable reversed

[#1]

Hi

I have the following table:

Entity	Week	hour
A	1	32
A	2	34
A	3	28
A	4	49
B	1	12

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

Entity	1	2	3	4	...
A	32	34	28	49
B	12

Is this possible witout reccursive joins...?

Dror

[Anonymous]

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]

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.

[#4]

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]

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

[#6]

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

[sivarajs]

Refer this thread http://community.qlik.com/message/172134

[kumarnatarajan]

Hi,

Refer this url

http://community.qlik.com/thread/23968

[jeffmartins]

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 · Accepted solution]

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