topic Re: Issue in Cross table in QlikView

Issue in Cross table

QlikNoviceNo1 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Issue in Cross table

QlikNoviceNo1 — Mon, 09 Jul 2018 23:56:05 GMT

Re: Issue in Cross table

qlikviewwizard — Tue, 10 Jul 2018 01:57:07 GMT

Hi,

Can you attach some sample data?

Or try like this:

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

LOAD *

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

Re: Issue in Cross table

QlikNoviceNo1 — Tue, 10 Jul 2018 02:36:36 GMT

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

Re: Issue in Cross table

QlikNoviceNo1 — Tue, 10 Jul 2018 02:40:59 GMT

Re: Issue in Cross table

qlikviewwizard — Tue, 10 Jul 2018 02:48:38 GMT

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

Re: Issue in Cross table

qlikviewwizard — Tue, 10 Jul 2018 03:03:08 GMT

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

Re: Issue in Cross table

QlikNoviceNo1 — Tue, 10 Jul 2018 03:23:57 GMT

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.

Re: Issue in Cross table

QlikNoviceNo1 — Tue, 10 Jul 2018 03:32:56 GMT

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

Re: Issue in Cross table

qlikviewwizard — Tue, 10 Jul 2018 03:47:44 GMT

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;

CrossTable(Month_Number, Data, 9)

LOAD *

FROM

(qvd) where 1=1;

Re: Issue in Cross table

qlikviewwizard — Tue, 10 Jul 2018 03:49:26 GMT

To eliminate Orphan tables in Crosstab Load, we need to use Where 1=1 condition.

You can see the output like the below.

Re: Issue in Cross table

QlikNoviceNo1 — Tue, 10 Jul 2018 18:38:59 GMT

wow! That did work.. Thanks..!

What is the logical explanation that these orphan tables got created? and how did the condition 1=1 eliminate them? It's kinda puzzling

Re: Issue in Cross table

qlikviewwizard — Wed, 11 Jul 2018 05:00:40 GMT

It is trick.