Incremental Load script error

Anonymous — Fri, 12 May 2017 08:48:16 GMT

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?

Re: Incremental Load script error

Fri, 12 May 2017 09:30:49 GMT

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

Kushal_Chawda — Fri, 12 May 2017 09:40:31 GMT

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')

Re: Incremental Load script error

Anonymous — Fri, 12 May 2017 09:46:18 GMT

Seems to work, thank you

topic Re: Incremental Load script error in QlikView

Incremental Load script error

Re: Incremental Load script error

Re: Incremental Load script error

Re: Incremental Load script error