topic Re: Timestamp Issues in QlikView

Timestamp Issues

Anonymous — Thu, 18 Oct 2012 15:10:41 GMT

I am having real trouble retrieving data from an Oracle database based on a date parameter. The date I am trying to interrogate is actually a timestamp in the format DD-MM-YYYY hh:mm:ss, but no matter what I try I am either getting

all the data, unfiltered
errors pertaining to "inconsistent datatypes: expected DATE got NUMBER"
errors pertaining to "literal does not match format string"

This is the current version of the code, and this returns all the data, unfiltered:

let vLatestQVDDate = Date#('01/01/2012','DD/YY/YYYY');

MovementDates:

LOAD MOVEMENT_DT;

sql Select DISTINCT FX.MOVEMENT_DT

FROM

CDW.FCT_XCS_BREAKDOWN_TR_DY FX

WHERE FX.MOVEMENT_DT > '$(vLatestQVDDate)'

ORDER BY FX.MOVEMENT_DT;

Can anyone offer any advice? We've used the {ts....} function around the filter value in other apps, but I can't seem to get it to work here.

Thanks

Andrew

Re: Timestamp Issues

MayilVahanan — Thu, 18 Oct 2012 15:26:44 GMT

I think u used wrongly..

let vLatestQVDDate = Date#('01/01/2012','DD/YY/YYYY');

Use like this

let vLatestQVDDate = Date#('01/01/2012','DD/MM/YYYY');

And verified the format for MOVEMENT_DT and vLatestQVDDate.

Hope that helps

Re: Timestamp Issues

Thu, 18 Oct 2012 15:34:50 GMT

Hi,

try this:

let vLatestQVDDate = Date#('01/01/2012','DD/MM/YYYY');

MovementDates:

LOAD MOVEMENT_DT;

sql Select DISTINCT FX.MOVEMENT_DT

FROM

CDW.FCT_XCS_BREAKDOWN_TR_DY FX

WHERE CONVERT(VARCHAR(10),FX.MOVEMENT_DT,110) > '$(vLatestQVDDate)'

ORDER BY CONVERT(VARCHAR(10),FX.MOVEMENT_DT,110);

Regards

Re: Timestamp Issues

Anonymous — Thu, 18 Oct 2012 15:35:56 GMT

Ah, sorry. That was just a typo on my part. Correctly assigning the value for vLatestQVDDate using Date# makes no difference, unfortunately.

This doesn't work either:

let vLatestQVDDate = Timestamp#('01/01/2012 00:00:00','DD-MM-YYYY hh:mm:ss');

(I get the "literal does not match format string" error)

Re: Timestamp Issues

Anonymous — Thu, 18 Oct 2012 15:40:06 GMT

Sorry, I'm getting a "missing expression" with that I'm afraid.

Re: Timestamp Issues

Thu, 18 Oct 2012 15:40:53 GMT

means when u load ur script ?

Regards

Re: Timestamp Issues

Thu, 18 Oct 2012 15:44:28 GMT

ahh, thats an Oracle database....i thot it was SQL Server

use

TO_DATE (TO_CHAR (FX.MOVEMENT_DT, 'DD/MM/YYYY'), 'DD/MM/YYYY')

instead of the Convert.

Regards

Re: Timestamp Issues

Anonymous — Fri, 19 Oct 2012 08:46:45 GMT

Thanks again, but sadly I'm still getting every available MOVEMENT_DT back with the data retrieval.