topic Re: Not able to convert a Column that is a date into a proper format in QlikView

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

pranaview — Sat, 16 Nov 2024 19:59:03 GMT

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

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

sunny_talwar — Mon, 30 Sep 2019 14:43:49 GMT

Try this instead

MonthName(Num#(Month)) as MonthYear

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

pranaview — Mon, 30 Sep 2019 15:23:40 GMT

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

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

sunny_talwar — Mon, 30 Sep 2019 15:43:41 GMT

May be try loading the file with *

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

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

pranaview — Tue, 01 Oct 2019 10:46:19 GMT

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

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

sunny_talwar — Tue, 01 Oct 2019 11:06:01 GMT

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.