topic Re: cross table file. in QlikView

cross table file.

prma7799 — Mon, 03 Aug 2015 12:43:56 GMT

Hi All,

How to upload below file into Qlikview using cross table functionality.

Thanks...

Re: cross table file.

marcus_sommer — Mon, 03 Aug 2015 12:52:50 GMT

This is part from table-wizard but it could be done manually, too. How, see here: The Crosstable Load.

- Marcus

Re: cross table file.

ogster1974 — Mon, 03 Aug 2015 13:02:24 GMT

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...

Re: cross table file.

sunny_talwar — Wed, 02 Sep 2015 11:35:55 GMT

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;

Re: cross table file.

prma7799 — Wed, 02 Sep 2015 11:46:29 GMT

Hi Sunny,

It is possible to take all data in pivot table as like excel sheet.

Thanks

Re: cross table file.

sunny_talwar — Wed, 02 Sep 2015 11:48:51 GMT

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;

Re: cross table file.

Anonymous — Wed, 02 Sep 2015 12:13:57 GMT

hi,

Please try:

multi_header_pivot_import.qvw