Issue in Cross table

[QlikNoviceNo1]

Hi all , I am trying to transform some data which is in a cross table format. Kinda new to cross tables hence the query.

The script that gets generated in the transformation step looks like below: I gave 9 as Qualifier fields parameter, Attribute field as Month and Data Field as No Of Employees. However, I am getting orphan columns in the data model pertaining to each month number.

CrossTable(Month, [No Of Employees], 9)

LOAD %Key,

     Year,

     [Airline ID],

     [Unique Carrier Code],

     [Unique Carrier],

     [Carrier Code],

     [Carrier Name],

     [Carrier Group ID (Old)],

     [Carrier Group ID],

     [01],

     [02],

     [03],

     [04],

     [05],

     [06],

     [07],

     [08],

     [09],

     [10],

     [11],

     [12]

FROM [Employment Statistics - CrossTable.qvd] (qvd);

[QlikNoviceNo1]

[qlikviewwizard]

Hi,

Can you attach some sample data?

Or try like this:

CrossTable(Month, [No Of Employees], 9)

LOAD *

FROM [Employment Statistics - CrossTable.qvd] (qvd);

[QlikNoviceNo1]

hi arjun, thanks for the reply. I tried with a *. Didn't work. Let me share the data.

[QlikNoviceNo1]

[qlikviewwizard]

Please attach some sample data.Screenshot won't help.

[qlikviewwizard]

Hi,

Please use this.I used some dummy data.

CrossTable:

CrossTable(MonthNumber, Data, 9)

LOAD * FROM

[Employment Statistics - CrossTable.xls]

(biff, embedded labels, table is Data$);

[QlikNoviceNo1]

ok. here we go . Here is the qvd am using. I am having a PE version of QV12. Hence won't be able to open your QVWs.

[QlikNoviceNo1]

I am attaching the qvw as well. Just in case. I tried this using the transformation wizard option.

[qlikviewwizard · Accepted solution]

Hi,

Please use code like this.

CrossTable(Month_Number, Data, 9)

LOAD %Key,

    Year,

    [Airline ID],

    [Unique Carrier Code],

    [Unique Carrier],

    [Carrier Code],

    [Carrier Name],

    [Carrier Group ID (Old)],

    [Carrier Group ID],

    [01],

    [02],

    [03],

    [04],

    [05],

    [06],

    [07],

    [08],

    [09],

    [10],

    [11],

    [12]

FROM

(qvd) where 1=1;

Or

CrossTable(Month_Number, Data, 9)

LOAD *

FROM

(qvd) where 1=1;