topic displaying dates in list box in QlikView

displaying dates in list box

felcar2013 — Thu, 17 Jul 2014 09:58:15 GMT

i am working with an Oracle database and i see that the timestamps for different fields are shown in qlikview either as number or timestamp indistinctively, but to be honest i do not understand the logic. I understan qlikview treats the date values as number or text, then if i load the data into qlikview without an Interpretation function, the data shall be read as number or string, but not indistinctively, like showed in the list box attached, To be on the save side, do i always need to use the Interpretation function? why is qlikview displaying the date as number and as date in the same list box?

Re: displaying dates in list box

tresB — Thu, 17 Jul 2014 10:07:21 GMT

SET DateFormat='DD/MM/YYYY';

You get something like this at the top of the script. This defines your default date format for the application. If the source data matches the format it would read the underlaying numeric values formatted as date otherwise as it is as simple numbers. For your case, I guess your source data has got two different formats of data.

Re: displaying dates in list box

ashfaq_haseeb — Thu, 17 Jul 2014 10:07:41 GMT

Hi,

What is your time stamp format in oracle?

You may try something like this

Load

F1,

F2,

DATE(DATE#(YOUR_DATE,'DD/MM/YYYY hh:mm:ss[.fff]'),'DD/MM/YYYY hh:mm:ss[.fff]') as Date,

F3;

sql select * from table.

Regards

ASHFAQ

Re: displaying dates in list box

Anonymous — Thu, 17 Jul 2014 10:08:52 GMT

in the load script do something like :

date(floor([YourDate])) as [DateField] ,

time(frac([YourDate])) as [TimeField] ,

Re: displaying dates in list box

hic — Thu, 17 Jul 2014 10:08:54 GMT

QlikView fields are not type cast, i.e. a field can have a mixture of types. Why QlikView has recognized the format for one of the values, but not for the others, I don't know.

The solution is anyway simple. Use one of the two following

Load the field using a formatting function: Timestamp(%CONTACT_TIMESTAMP)
Format it in the UI under Properties - Number

HIC

Re: displaying dates in list box

felcar2013 — Thu, 17 Jul 2014 10:24:40 GMT

thanks all for the answers

Interpretation function seems to work well, Need to check data source formats