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

Incremental Load with Variables and SQL 2005 Urgent

I seem to be having an issue with Incremental Load and using the Today() function with SQL 2005 database.

((changedate) >= $(LastExecTime) AND (changedate) < $(ThisExecTime))

the above is not working as this morning it did not load any changes made yesterday.

I am guessing that the variable:

Let ThisExecTime=(today());

is returning the date 5/2/2012 so I am assuming that the above (According to the log file)  formula is now showing:

((changedate) >= 5/1/2012 AND (changedate) < 5/2/2012)

Now, I am guessing that qlikview does not see the above BOLDS as dates when it runs the script, but it does not produce an error and loads everything from the QVD File just fine.

In the Qlikview Reference document it says to use:

ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(ThisExecTime)#

When I try the above: ((changedate) >= #$(LastExecTime)# AND (changedate) < #$(ThisExecTime)#)

((changedate) >= #5/1/2012# AND (changedate) < #5/2/2012#)

The script Fails with just an error in the log

General Script Error

5/2/2012:       Execution Failed

5/2/2012:      Execution finished.

Next I tried to tell it that the variable is a date using:

Let ThisExecTime= 'Date('&(today())&')';

However I still get the same above error, and am guessing it is because Date() is not recognized in SQL 2005

Does anyone have any ideas on what I should do here? Somewhat urgent as without this worjking 2 dashboards are now displaying the wrong data results.

1 Solution

Accepted Solutions
Not applicable

Re: Incremental Load with Variables and SQL 2005 Urgent

I have also just tried this:

((CAST((  STR( ( YEAR(changedate) ) ) + '/' +  STR( MONTH(changedate) ) + '/' +  STR( DAY(changedate) )  ) AS DATETIME) ) >= CAST((  STR( ( 2012 ) ) + '/' +  STR( (5) ) + '/' +  STR((1) )  ) AS DATETIME)  AND (CAST((  STR( ( YEAR(changedate) ) ) + '/' +  STR( MONTH(changedate) ) + '/' +  STR( DAY(changedate) )  ) AS DATETIME) ) <= CAST((  STR( ( 2012 ) ) + '/' +  STR( (May) ) + '/' +  STR((2) )  ) AS DATETIME) ) 

Trying to convert to SQL terms so the format is the same, however it still fails with just a General Script Error. Really wish this error was more descriptive.

Edit: Just noticed in the log that it saved the variable month(today()) as the monthname MAY. changed it to use num(month(today())) and it is working.

1 Reply
Not applicable

Re: Incremental Load with Variables and SQL 2005 Urgent

I have also just tried this:

((CAST((  STR( ( YEAR(changedate) ) ) + '/' +  STR( MONTH(changedate) ) + '/' +  STR( DAY(changedate) )  ) AS DATETIME) ) >= CAST((  STR( ( 2012 ) ) + '/' +  STR( (5) ) + '/' +  STR((1) )  ) AS DATETIME)  AND (CAST((  STR( ( YEAR(changedate) ) ) + '/' +  STR( MONTH(changedate) ) + '/' +  STR( DAY(changedate) )  ) AS DATETIME) ) <= CAST((  STR( ( 2012 ) ) + '/' +  STR( (May) ) + '/' +  STR((2) )  ) AS DATETIME) ) 

Trying to convert to SQL terms so the format is the same, however it still fails with just a General Script Error. Really wish this error was more descriptive.

Edit: Just noticed in the log that it saved the variable month(today()) as the monthname MAY. changed it to use num(month(today())) and it is working.

Community Browser