Regarding Converting Integer to MMM YYYY format

[Anonymous]

I have created one pivot/Crosstab file in the excel with the below screenshot. when i import the file into the Load editor i'm able to see the below screen.

SourceFile Format:

Load Editor format :

i'm unable to find the functions of converting the ( 41640,41671,.........) values to the above excel MON-YYYY format in my Table.

Where i want to get the below format after loading.

Need some Date conversation functions to work on these kind of conversations.

Thanks

Rams.

[sunny_talwar · Accepted solution]

Add a resident load after your above script like this

FinalTable:

NoConcatenate

LOAD Product,

     Date(Num#(Month), 'MMM YYYY') as Month,

     Sales

Resident ....;

DROP Table ....;

[sunny_talwar]

I assumed you are already using CrossTable load... but it seems you are not... look here on how to use crosstable load

The Crosstable Load

[its_anandrjs]

After using crosstable load date columns converted into num dates then you have to use Date and Date#

Then try this after Cross table load

Final:

NoConcatenate

LOAD

Product,

Date(Date#(Num#(Month),'MMM YYYY')'MMM YYYY') as Month,

Sales

Resident CrosstableName;

DROP Table CrosstableName;

[MK9885]

As Sunny mentioned maybe below one should work??

Table:

CrossTable (MonthYear , Sales,1) 

LOAD Product,

     [Jan-14],

     [Feb-14],

     [Mar-16],

     [Apr-13]

     Etc....

FROM......;

[Anonymous]

Sunny, Thanks for your kind response.

This one works perfect "Date(Num#(Month), 'MMM YYYY') as Month"

Thanks,

Rams

[Anonymous]

Anand, Thanks for your kind response.

When i try with the "Date(Date#(Num#(Month),'MMM YYYY')',MMM YYYY') as Month" I'm getting nulls in the field.

As Sunny mentioned ""Date(Num#(Month), 'MMM YYYY') as Month"", This is working fine as my expectation.

Thanks,

Rams.

[Anonymous]

Aehman, Thanks for your kind reply.

Ya, Sunny mentioned is working.

[its_anandrjs]

Can you provide a sample to have a look m code is working as usual properly with this code, sunny code is also working may be some format issue.

[Anonymous]

Anand, please see the below Code.

Output:

--Rams