topic Trying to use a timestamp to load new data in QlikView

Trying to use a timestamp to load new data

Mon, 12 Oct 2009 18:58:40 GMT

Hi,

I am extracting data from an SQL database and I store this in a QVD. I would like to use a field from the sql-database with the name time_stamp to filter new data from the database.

The time_stamp field in the database has this format: '2009-03-31 00:00:00.000'

Using


LOAD * FROM table WHERE time_stamp > '2009-03-31 00:00:00.000';

works and if I insert another date it works also

to use the last reload timestamp I use



LET


 ThisExecTime = Reloadtime();

.
 .
 LOAD * FROM table WHERE time_stamp > $(LastExecTime);

Let LastExecTime = ThisExecTime;

and i get an error, incorect statement, using #$(LastExecTime) gives the same error.

I am missing something but cant really figure it out.

Could anyone please help me a bit

Trying to use a timestamp to load new data

Mon, 12 Oct 2009 19:32:43 GMT

I think there are two problems...

The statement WHERE time_stamp > $(LastExecTime); needs to have single quotes round the variable, i.e.

... WHERE time_stamp > '$(LastExecTime)';

because you are passing a statement to the database to process as SQL, and SQL needs the date in quotes.

Secondly, if LastExecTime does not exist when you first run the script, it will not expand into anything. Try setting a default value before you need to use it.

Trying to use a timestamp to load new data

Mon, 12 Oct 2009 19:43:22 GMT

The first run I had covered by indeed assigning a default value.

The addition of the ' before and after did it. Thanks!

Trying to use a timestamp to load new data

Tue, 13 Oct 2009 14:03:57 GMT

Another thing you can try is to ensure that the timestamp is ready like a timestamp, eg. by using timestamp#(2009-03-31 00:00:00.000) or the date#()-function. Good luck!