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
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
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.
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!