topic Re: crosstable reversed in QlikView

crosstable reversed

Thu, 06 Jun 2013 10:41:46 GMT

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

Re: crosstable reversed

Anonymous — Thu, 06 Jun 2013 10:45:51 GMT

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

Re: crosstable reversed

kumarnatarajan — Thu, 06 Jun 2013 10:55:41 GMT

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.

Re: crosstable reversed

Thu, 06 Jun 2013 10:58:15 GMT

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

Re: crosstable reversed

sivarajs — Thu, 06 Jun 2013 11:47:03 GMT

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

Re: crosstable reversed

Thu, 06 Jun 2013 11:51:49 GMT

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

Re: crosstable reversed

sivarajs — Thu, 06 Jun 2013 11:58:03 GMT

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

Re: crosstable reversed

kumarnatarajan — Thu, 06 Jun 2013 12:06:05 GMT

Hi,

Refer this url

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

Re: crosstable reversed

jeffmartins — Fri, 07 Jun 2013 17:58:42 GMT

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

Re: crosstable reversed

Sokkorn — Sun, 09 Jun 2013 04:08:58 GMT

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

Re: crosstable reversed

Sun, 09 Jun 2013 09:57:43 GMT

Bingo! This exactly what i was looking for thanks!

Op 9 jun. 2013 06:09 schreef "Sokkorn Cheav (Cambodia)" <

qcwebmaster@qlik.com> het volgende:

**
QlikCommunity <http://community.qlik.com/index.jspa> Re:
crosstable reversed created by Sokkorn Cheav (Cambodia)<http://community.qlik.com/people/sokkorn.cheav>in
Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/354871#354871>

Re: crosstable reversed

Sokkorn — Sun, 09 Jun 2013 10:01:14 GMT

Hi Dror,

Can you close this discussion?

Regards,Sokkorn