Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikNoviceNo1
Contributor III
Contributor III

Issue in Cross table

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);

1 Solution

Accepted Solutions
qlikviewwizard
Master II
Master II

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;

View solution in original post

12 Replies
QlikNoviceNo1
Contributor III
Contributor III
Author

image.PNG

qlikviewwizard
Master II
Master II

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
Contributor III
Contributor III
Author

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

QlikNoviceNo1
Contributor III
Contributor III
Author

data.PNG

qlikviewwizard
Master II
Master II

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

qlikviewwizard
Master II
Master II

Hi,

Please use this.I used some dummy data.

1.png

2.png

CrossTable:

CrossTable(MonthNumber, Data, 9)

LOAD * FROM

[Employment Statistics - CrossTable.xls]

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

QlikNoviceNo1
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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

qlikviewwizard
Master II
Master II

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;