Not able to convert a Column that is a date into a proper format

[pranaview]

Hi Guys,

So, I have a source file which has the date something like below

Comment           Mar/19              Apr/19             May/19    and so on....

A                               459                       344                  114

B                               345                       23                    344

C                               55                         555                   34

D                               7                           323                   67

The column B,C,D and so on are actually Dates in (MM/DD/YYYY) but when i'm loading them they are coming in Number format like below

LOAD Code,
[43543],
[43574],
[43604]

I am doing a CrossTable(Month,Value,1) on this table and using Resident  load to fetch the newly transformed table but after the Resident load, Month will have the values 43543,43574,43604 etc and when i'm trying to convert it into a proper date format such as Mar-19,Apr-19 etc using Date functions, i am getting null for the whole column.

The only way i'm able to do it if the Column B,C and D are in a format such as 03.01.2019,04.01.2019,05.01.2019 and so on. Now i am able to simply convert it using the following functions

Monthname(Date(Date#(Month,'MM.DD.YYYY'),'MM/DD/YYYY')) 

But this would be a change in the source file itself which is why i want to avoid it and instead trying to find out why it is not working with the original columns.

Any help or suggestion will be appreciated.

Thanks,
Pranav 

[sunny_talwar · Accepted solution]

Try this instead

MonthName(Num#(Month)) as MonthYear

[pranaview]

Sunny, thanks for the solution. It worked. 

But i have come across another problem. so, like every month source file will have a new column with the different month but my original Load script obviously does not have that at this moment, so that column will not be picked unless i make the change in the script on the fly which i would have to do every month, doesn't seem a good way to go about it. Another option is to simply add columns for future months without any data so my script has them and once that data fills in, it will display but i am still not sure how should i implement this, if you have any thought then please let me know.

I'll mark your response as correct cos it is the solution to the problem i asked. Thanks again!

Pranav 

[sunny_talwar]

May be try loading the file with *

CrossTable (....)
LOAD *
FROM ....

[pranaview]

For some reason i was in the impression that * doesn't work with CrossTable(). Thanks for clearing up that Sunny.

[sunny_talwar]

It does work, but the only challenge is the order of your columns, if for some reason that is not correctly specified in the Excel, things become complicated.