topic Re: why crosstable Month cannot show in monthname in QlikView

why crosstable Month cannot show in monthname

Wed, 20 May 2015 05:15:10 GMT

Hi guys,

when i use crosstable to load Month, i cannot set it as monthname(Month) to show MMM-YYYY

Here's my script:

CrossTable

(Month, Plant, 1)
LOAD Plant,
[1/15/2014],
[2/15/2014],
[3/15/2014],
[4/15/2014],
[5/15/2014],
[6/15/2014],
[7/15/2014],
[8/15/2014],
[9/15/2014],
[10/15/2014],
[11/15/2014],
[12/15/2014],
[1/15/2015],
[2/15/2015],
[3/15/2015],
[4/15/2015],
[5/15/2015],
[6/15/2015],
[7/15/2015],
[8/15/2015],
[9/15/2015],
[10/15/2015],
[11/15/2015],

[12/15/2015]

Re: why crosstable Month cannot show in monthname

jonathandienst — Wed, 20 May 2015 05:22:32 GMT

As you cannot add a preceding load to a crosstable load, you will need to do something like this:

Data:

CrossTable (T_Month, Plant, 1)

LOAD Plant,

[1/15/2014],

[2/15/2014],

[3/15/2014],

[4/15/2014],

...

;

Join(Data)

LOAD Distinct

T_Month,

Date(Date#(T_Month, 'M/DD/YYYY'), 'MMM-YYYY') As Month

Resident Data;

DROP Field T_Month;

Re: why crosstable Month cannot show in monthname

jagan — Wed, 20 May 2015 05:46:38 GMT

Hi,

Try like this

Data:

CrossTable(Date, Plant, 1)
LOAD *

FROM DataSource;

Left Join(Data)

Load DISTINCT

Date,

Date(MonthStart(Date), 'MMM-YYYY') AS Month

RESIDENT Data;

Re: why crosstable Month cannot show in monthname

Wed, 20 May 2015 08:31:02 GMT

Hi Jonathan,

Thank you for quick reply. How can I combine the Month data together from different datasource?

LOAD Month,

Rate

...

From DataSource1;

CorssTable(Month, Plant,1)

LOAD Plant

[1/15/2014],

[2/15/2014],

[3/15/2014],

[4/15/2014],

...

From DataSource2;

Re: why crosstable Month cannot show in monthname

Wed, 20 May 2015 08:32:18 GMT

Thank you Jagan. Same question to Jonathan. Could you kindly help me.

Re: why crosstable Month cannot show in monthname

jagan — Wed, 20 May 2015 08:38:41 GMT

Hi,

Try like this

Data:

LOAD Month,

Rate

...

From DataSource1;

Temp:

NoConcatenate

CorssTable(Month, Plant,1)

LOAD Plant

[1/15/2014],

[2/15/2014],

[3/15/2014],

[4/15/2014],

...

From DataSource2;

Concatenate(Date)

LOAD Plant,

Date(MonthStart(Month), 'MMM-YYYY') AS Month,

Plant

RESIDENT Temp;

DROP TABLE Temp;

Regards,

Jagan.

Re: why crosstable Month cannot show in monthname

Wed, 20 May 2015 11:24:44 GMT

Hi Jagan, I tried but it doesn't work. Please find attached files on top of the page. Could you kindly show me how to wirte this script in corret way?

Thank you soooo much for your help.

Re: why crosstable Month cannot show in monthname

jagan — Thu, 21 May 2015 04:18:38 GMT

Hi,

I have changed your dimension Month to =Date(MonthEnd(Alt(Date#(Month, 'MM/DD/YYYY'), Date(Month))), 'MMM-YYYY') in your file, now it is working as expected.

Also please find attached file for solution.

If you got the answer please close this post by clicking Useful and Correct Answers options for the posts which helps you in finding the solution.

Regards,

Jagan.

Re: why crosstable Month cannot show in monthname

Tue, 26 May 2015 05:58:04 GMT

Thank you very much Jagan