topic Re: Cross Table in Qlikview in QlikView

Cross Table in Qlikview

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

Hi Every One,

Can any one please help me on How to do the cross load in qlikview with below attached file.

I need one separate column for Month with jul16...

Thanks in Advance

Re: Cross Table in Qlikview

Kushal_Chawda — Fri, 08 Sep 2017 09:25:09 GMT

Crosstable(Month,Value,3)

LOAD *

FROm table;

The Crosstable Load

Re: Cross Table in Qlikview

sunny_talwar — Fri, 08 Sep 2017 09:32:15 GMT

Here is the complete script

Table:

CrossTable (Month, Value, 3)

LOAD Project,

Location,

Job,

[Jul-16],

[Aug-16],

[Set-16],

[Oct-16],

[Nov-16],

[Dec-16],

[42736],

[42767],

[42795],

[42826],

[42856],

[42887],

[42917],

[42948],

[42979]

FROM

[..\..\..\Downloads\Cross Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD Project,

Location,

Job,

Date(MonthStart(Alt(Num(Date#(Month, 'MMM-YY')), Num(Num#(Month)))), 'MMM-YY') as MonthYear,

Num(Value) as Value

Resident Table;

DROP Table Table;

One of your field name is incorrect (Set-16 instead of Sep-16), otherwise all the MonthYear are ready correctly.

Best,

Sunny

Re: Cross Table in Qlikview

jmvilaplanap — Fri, 08 Sep 2017 10:26:43 GMT

It will be easier if you use the wizard,

Re: Cross Table in Qlikview

its_anandrjs — Fri, 08 Sep 2017 10:36:49 GMT

Check the attached and script as well will it worked for you, When loading data in qlik your monhname field contains both NUM and String dates so for converting them you need ALT with multiple date formats to load data properly see script over here.

CrossMainTab:

CrossTable(MonthNames, Data, 3)

LOAD Project,

Location,

Job,

[Jul-16],

[Aug-16],

[Set-16],

[Oct-16],

[Nov-16],

[Dec-16],

[42736],

[42767],

[42795],

[42826],

[42856],

[42887],

[42917],

[42948],

[42979]

FROM

[Cross Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Final:

LOAD

Alt( Date(NUM#(MonthNames,'#####'),'MMM-YY'),Date(DATE#(MonthNames,'MMM-YY'),'MMM-YY')) as Month_Name

Resident CrossMainTab;

DROP Table CrossMainTab;

DROP Field MonthNames;

Re: Cross Table in Qlikview

bhavvibudagam — Fri, 08 Sep 2017 10:54:18 GMT

Hi Sunny,

Thanks for your reply.

Can you please explain CrossTable (Month, Value, 3) in that why we have consider 3 at value.

Thanks

Re: Cross Table in Qlikview

its_anandrjs — Fri, 08 Sep 2017 10:59:44 GMT

Dear take opportunity to explain if you loading the Cross Table load Your few columns will be Rows from Columns and Some Rows and Columns into single field. If you loading this data by Cross Load Wizard you get helped for this.

Ex:-

Crosstable(MonthNames,Data,3)

Re: Cross Table in Qlikview

Kushal_Chawda — Fri, 08 Sep 2017 11:12:23 GMT

Please read the the article on the link which I have given in my reply above

Re: Cross Table in Qlikview

bhavvibudagam — Fri, 08 Sep 2017 11:19:38 GMT

Thanks Anand