row records become a column?! Reverse CrossTable structure?

[#1]

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

[jonathandienst]

Hi

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

[#3]

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

[Sokkorn · Accepted solution]

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

[#5]

Thanks!!!

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