Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Date values sometimes display as numbers

I have several columns that return a DATETIME value out of a SQL Server 2008 R2 database.

All of the columns either return a valid SQL date (i.e. YYYY-MM-DD HH:MM:SS.SSS) or NULL.

Some of the columns display properly as a date (i.e. 2012-05-04 00:00:00.000 displays as 5/4/2012 12:00:00 AM), but others display as numbers (i.e. 2012-05-31 00:00:00.000 displays as 41060).

It seems like the more NULL data that is loaded the less likely QV is treat a date column as a date.

I understand that under the covers QV treats dates as numbers thus allowing for the data to be displayed in multiple ways.  And I've seen that forcing the format might fix this (haven't had the chance to build a test app to prove that yet), but it's not much of an option for our solution (we allow a variable amount of columns and so rely heavily on LOAD *).

Any ideas on what might be causing this and how I might work around it?

If it helps we're using QV 10.0.4 on the server our application is running on.

Tags (1)
4 Replies
nilesh_gangurde
Valued Contributor

Re: Date values sometimes display as numbers

Hi,

QlikView takes the date in the Julian format.

the no. which you are getting is also a date but its in the julian form.

To get the format of date follow the steps given below:

Go to Properties => Number Tab => Click on Override Document Settings => then Click on date.

Or

If you want at load time then,

You can create the master calender. The date function will give the date as you require.

Refere http://community.qlik.com/message/223140 to create the master calender

Hope this will help.

Regards,

Nilesh Gangurde

Not applicable

Re: Date values sometimes display as numbers

Thanks Nilesh.

Already knew about the override document settings and that will work but is considered unacceptable by the project managers.

I will look into the master calendar idea and see if that works, though I'm pretty sure any drastic changes to our load scripts will be ruled unacceptable by the project managers.

The question that they still want answered is why do some dates work "properly" and some don't.

Re: Date values sometimes display as numbers

Hi,

It's always a good practice to use the LOAD sentence to control what you are putting into memory in addition to the SQL SELECT (what you are pulling from the database). In the LOAD sentence you can always use the Date() function to say QlikView to display that value with date format.

Or is that you are already using LOAD and Date() but it still shows some values as dates and some as numeric?

Hope that helps.

Miguel

Highlighted
qvraj123
Contributor II

Re: Date values sometimes display as numbers

Hi Miguel,

Tab1:

LOAD

Date(floor(ColA),'MM/DD/YYYY') AS TestDate

FROm QVD;

Tab2:

LOAD

*,

IF () as Test1

If () as Test2

FROM Tab1

does this kind of loading again will change the date format? thanks a lot for your time

within the load statement I used Date(floor(ColA),'MM/DD/YYYY') AS TestDate but still when I go to the sheet and put this column in table box or list box it is showing as numbers;