Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Format Date with apostrophe

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

1 Solution

Accepted Solutions
erich_shiino
Honored Contributor

Format Date with apostrophe

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

4 Replies
vupen
Contributor

Re: Format Date with apostrophe

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)

;

Not applicable

Format Date with apostrophe

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

erich_shiino
Honored Contributor

Format Date with apostrophe

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

Not applicable

Format Date with apostrophe

That's it !

Thanks a lot !

Community Browser