cross table file.

[prma7799]

Hi All,

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

Thanks...

[marcus_sommer]

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

- Marcus

[ogster1974]

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

[sunny_talwar]

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;

[prma7799]

Hi Sunny,

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

Thanks

[sunny_talwar · Accepted solution]

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;

[Anonymous]

hi,

Please try:

multi_header_pivot_import.qvw