topic Re: Format Date with apostrophe in QlikView

Format Date with apostrophe

Thu, 22 Mar 2012 09:09:34 GMT

Hello,

I like to get the max(dateofday) from an existing qvd file and use this date in my SQL against the database to get the missing data up to the current date.

To query the database (Teradata) I need the SQL where clause like " where dateofday > '2012-03-15' ".

To get the max date from the qvd is no problem, but how can I get the apostrophe around the date value ?

Thanks !

Amigoser

Re: Format Date with apostrophe

vupen — Thu, 22 Mar 2012 09:20:23 GMT

Please try this:

Let LastFullLoadDate = Date(Peek('LastQVD.LastLoadDate',0,LastQVD),'DD-MMM-YYYY');
Let LastFullLoadDateNum = Num(LastFullLoadDate);
Let vMyDateFilter = ' WHERE MyDateField  between ' & Chr(39) & LastFullLoadDateNum & Chr(39) & ' and sysdate '; 
NewData:
Load
*
From
MyTable
$(vMyDateFilter)
;

Format Date with apostrophe

Thu, 22 Mar 2012 16:02:05 GMT

OK, now I get the following DB error:

ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] A character string failed conversion to a numeric value.

And the SQL looks like :

SELECT ...

FROM "DB"."TABLE"

WHERE dateofday between '' and DATE

Format Date with apostrophe

erichshiino — Thu, 22 Mar 2012 16:13:15 GMT

HI, You can try this :

tmpMax:

Load max(dateofday) as MaxDay

from

yourqvd.qvd (qvd);

vMaxDate = text(date( peek('MaxDay'), 'YYYY-MM-DD' );

drop table tmpMax;

sql select * from table where dateofday > '$(vMaxDate)'

Hope this helps,

Erich

Format Date with apostrophe

Sat, 24 Mar 2012 19:59:46 GMT

That's it !

Thanks a lot !