Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
floryan01
New Contributor II

Incremental Load script error

Hey,

I'm trying to implement incremental loading from SQL to QVD.

I built an initial load writer (QVD_Writer_Bewegungen2.qvw) that works fine and gives me a QVD-File called 'BEWEGUNGEN2'.

--------------------------------------------------------------------------

V_QLIK_BEWEGUNGEN2:

SQL SELECT *

FROM TEXAS.V_QLIK_BEWEGUNGEN2

WHERE    BEW_BEWEGUNGSTAG>= '$(Start_Zeitraum)' //Start of Data

;

store V_QLIK_BEWEGUNGEN2 into '..\QVD\V_QLIK_BEWEGUNGEN2.qvd' (qvd);

drop table V_QLIK_BEWEGUNGEN2;

------------------------------------------------------------------------------------------

Now I want to build an incremental load writer script (QVD_Writer_Bewegungen2_incr.qvw) that updates my data.

I therefore built a view (also used in the initial load) that contains:

ID - Unique ID for each line of data

AENDERUNGSDATUM - Edit date of the line of data

DATA1 - Contains Data i want to analyze

DATA2 - Contains Data i want to analyze

...

My script for the incremental Load:

------------------------------------------------------------------------------------------------------------------------------------------

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

OLEDB CONNECT TO [...]

let Start_Zeitraum = addmonths(YearStart(AddMonths(today(),-1), -2 ),1);    //Start of Data

Let ThisExecTime = ReloadTime();

V_QLIK_BEWEGUNGEN2:

SQL SELECT * FROM TEXAS.V_QLIK_BEWEGUNGEN2

WHERE AENDERUNGSDATUM >= '$(LastExecTime)'

      AENDERUNGSDATUM < '$(ThisExecTime)'

     AENDERUNGSDATUM >= '$(Start_Zeitraum)'

;

Concatenate LOAD * FROM [..\QVD\V_QLIK_BEWEGUNGEN2.qvd] (qvd)

WHERE NOT EXISTS(ID);

Inner Join SQL SELECT ID FROM TEXAS.V_QLIK_BEWEGUNGEN2;

If ScriptErrorCount = 0 then

    STORE V_QLIK_BEWEGUNGEN2 INTO '..\QVD\V_QLIK_BEWEGUNGEN2.qvd' (qvd);

    Let LastExecTime = ThisExecTime;

End If;

drop table V_QLIK_BEWEGUNGEN2;

---------------------------------------------------------------------------------------------------------------------------------------------

But then i get a strange Error right after executing:

Can you help me out please?

1 Solution

Accepted Solutions

Re: Incremental Load script error

you need to have same Date format for SQL date field and variable created in QlikView

let Start_Zeitraum = date(addmonths(YearStart(AddMonths(today(),-1), -2 ),1),'DD/MM/YYYY');    //Start of Data

Let ThisExecTime = date(ReloadTime(),'DD/MM/YYYY');


WHERE

to_date(to_char(AENDERUNGSDATUM,'DD/MM/YYYY'),'DD/MM/YYYY') >= to_date('$(Start_Zeitraum)','DD/MM/YYYY') AND

to_date(to_char(AENDERUNGSDATUM,'DD/MM/YYYY'),'DD/MM/YYYY') < to_date('$(ThisExecTime)','DD/MM/YYYY')

View solution in original post

3 Replies
Not applicable

Re: Incremental Load script error

Hi Florian,

This is due to the fact that your variable '$(LastExecTime)' does not have any value. You need to find and save the value for the variable just as you have done for the other two.

Cheers,

Rohan

Re: Incremental Load script error

you need to have same Date format for SQL date field and variable created in QlikView

let Start_Zeitraum = date(addmonths(YearStart(AddMonths(today(),-1), -2 ),1),'DD/MM/YYYY');    //Start of Data

Let ThisExecTime = date(ReloadTime(),'DD/MM/YYYY');


WHERE

to_date(to_char(AENDERUNGSDATUM,'DD/MM/YYYY'),'DD/MM/YYYY') >= to_date('$(Start_Zeitraum)','DD/MM/YYYY') AND

to_date(to_char(AENDERUNGSDATUM,'DD/MM/YYYY'),'DD/MM/YYYY') < to_date('$(ThisExecTime)','DD/MM/YYYY')

View solution in original post

floryan01
New Contributor II

Re: Incremental Load script error

Seems to work,  thank you