Cross Table in Qlikview

[bhavvibudagam]

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

[Kushal_Chawda]

Crosstable(Month,Value,3)

LOAD *

FROm table;

The Crosstable Load

[sunny_talwar · Accepted solution]

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

[jmvilaplanap]

It will be easier if you use the wizard,

[its_anandrjs]

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;

[bhavvibudagam]

Hi Sunny,

Thanks for your reply.

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

Thanks

[its_anandrjs]

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)

[Kushal_Chawda]

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

[bhavvibudagam]

Thanks Anand