Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Master II
Master II

Re: Issue in Cross table

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

Re: Issue in Cross table

image.PNG

Highlighted
Master II
Master II

Re: Issue in Cross table

Hi,

Can you attach some sample data?

Or try like this:

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

LOAD *

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

Highlighted
Contributor III
Contributor III

Re: Issue in Cross table

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

Highlighted
Contributor III
Contributor III

Re: Issue in Cross table

data.PNG

Highlighted
Master II
Master II

Re: Issue in Cross table

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

Highlighted
Master II
Master II

Re: Issue in Cross table

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

Highlighted
Contributor III
Contributor III

Re: Issue in Cross table

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.

Highlighted
Contributor III
Contributor III

Re: Issue in Cross table

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

Highlighted
Master II
Master II

Re: Issue in Cross table

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