Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
How to upload below file into Qlikview using cross table functionality.
Thanks...
Yes, just remove the where clause from the Resident Load
Table:
CrossTable(Year, Data, 3)
LOAD @1 as ID,
@2 as StudentName,
@3 as Comments,
@4 & '|' & @5 as 2003,
@6 & '|' & @7 as 2004,
@8 & '|' & @9 as 2005,
@10 & '|' & @11 as 2006,
@12 & '|' & @13 as 2007,
@14 & '|' & @15 as 2008,
@16 & '|' & @17 as 2009,
@18 & '|' & @19 as 2010,
@20 & '|' & @21 as 2011,
@22 & '|' & @23 as 2012,
@24 & '|' & @25 as 2013,
@26 & '|' & @27 as 2014,
@28 & '|' & @29 as 2015,
@30 & '|' & @31 as 2016,
@32 & '|' & @33 as 2017
FROM
Abnormal_Movement.xls
(biff, no labels, header is 2 lines, table is Abnormal_Movement$);
NewTable:
LOAD ID,
StudentName,
Comments,
Year,
SubField(Data, '|', 1) as School,
SubField(Data, '|', 2) as Level
Resident Table;
Where Data <> '|';
DROP Table Table;
This is part from table-wizard but it could be done manually, too. How, see here: The Crosstable Load.
- Marcus
Load your data up as follows
ID,
StudentName,
Comments,
2013 AS Year,
2013_School AS School,
2013_Level AS level
Concatenate each time you need to add a year in from your excel sheet. The data will then be loaded in a state for you to use in a pivot table or any other charts. I would lose row 1 and rename your row hears to include the year so 2003_School, 2004_School etc...
Looking for this?
Script:
Table:
CrossTable(Year, Data, 3)
LOAD @1 as ID,
@2 as StudentName,
@3 as Comments,
@4 & '|' & @5 as 2003,
@6 & '|' & @7 as 2004,
@8 & '|' & @9 as 2005,
@10 & '|' & @11 as 2006,
@12 & '|' & @13 as 2007,
@14 & '|' & @15 as 2008,
@16 & '|' & @17 as 2009,
@18 & '|' & @19 as 2010,
@20 & '|' & @21 as 2011,
@22 & '|' & @23 as 2012,
@24 & '|' & @25 as 2013,
@26 & '|' & @27 as 2014,
@28 & '|' & @29 as 2015,
@30 & '|' & @31 as 2016,
@32 & '|' & @33 as 2017
FROM
Abnormal_Movement.xls
(biff, no labels, header is 2 lines, table is Abnormal_Movement$);
NewTable:
LOAD ID,
StudentName,
Comments,
Year,
SubField(Data, '|', 1) as School,
SubField(Data, '|', 2) as Level
Resident Table
Where Data <> '|';
DROP Table Table;
Hi Sunny,
It is possible to take all data in pivot table as like excel sheet.
Thanks
Yes, just remove the where clause from the Resident Load
Table:
CrossTable(Year, Data, 3)
LOAD @1 as ID,
@2 as StudentName,
@3 as Comments,
@4 & '|' & @5 as 2003,
@6 & '|' & @7 as 2004,
@8 & '|' & @9 as 2005,
@10 & '|' & @11 as 2006,
@12 & '|' & @13 as 2007,
@14 & '|' & @15 as 2008,
@16 & '|' & @17 as 2009,
@18 & '|' & @19 as 2010,
@20 & '|' & @21 as 2011,
@22 & '|' & @23 as 2012,
@24 & '|' & @25 as 2013,
@26 & '|' & @27 as 2014,
@28 & '|' & @29 as 2015,
@30 & '|' & @31 as 2016,
@32 & '|' & @33 as 2017
FROM
Abnormal_Movement.xls
(biff, no labels, header is 2 lines, table is Abnormal_Movement$);
NewTable:
LOAD ID,
StudentName,
Comments,
Year,
SubField(Data, '|', 1) as School,
SubField(Data, '|', 2) as Level
Resident Table;
Where Data <> '|';
DROP Table Table;