topic Re: row records become a column?! Reverse CrossTable structure? in QlikView

row records become a column?! Reverse CrossTable structure?

Fri, 12 Jul 2013 08:01:20 GMT

Hi All

Did any guys experiece like as below

Orginial data from QVD:

ID	Region	Department	Cost
1	HK	MKT	500
2	USA	ACCT	1300
3	TW	HR	200

For some reason, i would like using scripts to change as below:

ID	Region	MKT	ACCT	HR
1	HK	500
2	USA		1300
3	TW			200

Can Qlikview is able to do it?

Many thanks

Mic

Re: row records become a column?! Reverse CrossTable structure?

jonathandienst — Fri, 12 Jul 2013 08:08:21 GMT

To do this in the front end, just create a pivot table with ID, Region and Department as dimensions and drag the Department column to the top of the chart (until the vertical red line changes to horizontal).

If you need to do this in script (although I dont know why you would want to do that), then check out generic loads in the manual.

Regards

Jonathan

Re: row records become a column?! Reverse CrossTable structure?

Fri, 12 Jul 2013 08:10:21 GMT

Thanks. However, i need to do it at the scripts level instead of front end

Re: row records become a column?! Reverse CrossTable structure?

Sokkorn — Fri, 12 Jul 2013 09:55:19 GMT

Hi Mic,

QlikView cannot do it. But we; developer can do it. Let try:

[DS]:
LOAD * Inline [
ID,    Region,    Department,    Cost
1,    HK,        MKT,        500
2,    USA,    ACCT,        1300
3,    TW,        HR,            200];
[Data]:
Mapping
LOAD ID ,Region Resident [DS];
[TMP1]:
GENERIC LOAD ID,Department,Cost 
RESIDENT [DS];
[RESULT]:
LOAD DISTINCT ID 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;
[Data]:
LOAD ApplyMap('Data',ID,Null())    AS Region, * Resident [RESULT];
DROP Table [RESULT];

Regards,

Sokkorn

Re: row records become a column?! Reverse CrossTable structure?

Wed, 17 Jul 2013 08:24:59 GMT

Thanks!!!

It works after apply. Let me study the syntax and how does it work. Many thanks