topic Re: Date Check During Incremental Load in QlikView

Date Check During Incremental Load

Wed, 11 Dec 2013 11:48:24 GMT

Hi Experts,

I am working on incremental load but i am facing a issue during the date check to load the fresh records.When i put the condition

"Where Modification Time >= LastExecTime"

I am able to capture the lastExecTime from the alredy loaded qvd and it is comming in MM-DD-YYYY format. But i am not able to match the above condition because of some format issue with the two dates.

Can you suggest some functions or some other solution which i should use with the field having the Modification time.I have already used Date function with MM-DD-YYYY format but it does not work.

Thanks in advance for your help

Re: Date Check During Incremental Load

Wed, 11 Dec 2013 13:57:03 GMT

Hi Ronny, The variable holds the value in string format. So you need to convert the variable value into desired date field format.

If DB is oracle:

"Where Modification Time >= TO_DATE('$(LastExecTime)','MM-DD-YYYY')"

Re: Date Check During Incremental Load

Thu, 12 Dec 2013 05:40:53 GMT

Hi Dathu

Thanks for your response.My DB is SQL, can you suggest some function which can be used with the variable when working with SQL DB.

Thanks

Re: Date Check During Incremental Load

Thu, 12 Dec 2013 07:53:58 GMT

Hi Ronny,

Can you try like below:

"Where Modification Time >= CONVERT(VARCHAR(10), '$(LastExecTime)', 110)"

I assumed your database is MS SQL Server.

Thanks,

Angad

Re: Date Check During Incremental Load

Thu, 12 Dec 2013 21:24:12 GMT

For SQL Server you can use chr(39) to surround your value:

Let vLoadTime = chr(39) & Date(Now(),'M/D/YYYY h:mm:ss[.fff] TT') & chr(39);

and then simply refer to the variables:

where lst_mod_ts >= $(vLastLoadTime)
and lst_mod_ts < $(vLoadTime);

Re: Date Check During Incremental Load

Anonymous — Thu, 12 Dec 2013 21:50:41 GMT

It is simpler in MS SQL. This always works for me:

Where Modification Time >= '$(LastExecTime)'

Re: Date Check During Incremental Load

Fri, 13 Dec 2013 02:17:15 GMT

If your DB is Oracle you can use TO_DATE or TO_TIMESTAMP functions to convert the strings into date formats.

If your DB SQL Server you dont need any function.

Re: Date Check During Incremental Load

Sat, 14 Dec 2013 17:02:27 GMT

Michael Solomovich and dathu.qv are right: MS SQL Server generally interprets the expanded value correctly as a date without the need for any functions. However, you DO need the single quotes around it!