Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks. However, i need to do it at the scripts level instead of front end
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
Thanks!!!
It works after apply. Let me study the syntax and how does it work. Many thanks