why crosstable Month cannot show in monthname

[#1]

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]

[jonathandienst]

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;

[jagan]

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;

[#4]

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;

[#5]

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

[jagan]

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.

[#7]

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.

[jagan · Accepted solution]

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.

[#9]

Thank you very much Jagan