topic Re: incremental load in QlikView

incremental load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All ,

PFA sample data. I have 4 months data. Jan- April

PFA script.

First initial load will execute and we will get jan data (11rows), it will be saved in .qvd format.

In Incremental load we will get feb data (11 rows), this will be concatenated with jan data based on Date modified date > prv month Date modified date & where not Exists (ID) ;

Once the reload done, we should get Feb (11 rows) + Jan(10 rows, since ABCDE16 has updated data in Feb month).

But im getting data as below. Plz help.

ECRID	ISRejected	DateModified
ABCDE16	Yes	1/3/2009
ABCDE16		1/3/2009
ABCDE21	Yes	1/14/2009
ABCDE21	Yes	1/14/2009
ABCDE21		1/14/2009
ABCDE22		1/16/2009
ABCDE23	Yes	1/26/2009
ABCDE23		1/26/2009
ABCDE24	Yes	1/28/2009
ABCDE25		1/30/2009
ABCDE26	Yes	1/31/2009	Feb data(11rows)
ABCDE12	Yes	12/26/2008
ABCDE15	Yes	12/27/2008
ABCDE17	Yes	12/27/2008
ABCDE18		12/26/2008
ABCDE19	Yes	12/29/2008
ABCDE20	Yes	12/31/2008	Jan data(6 rows)

I need it as below.

ECRID	ISRejected	DateModified
ABCDE16	Yes	1/3/2009
ABCDE16		1/3/2009
ABCDE21	Yes	1/14/2009
ABCDE21		1/14/2009
ABCDE22		1/16/2009
ABCDE23	Yes	1/26/2009
ABCDE23		1/26/2009
ABCDE24	Yes	1/28/2009
ABCDE25		1/30/2009
ABCDE26	Yes	1/31/2009	Feb data(11rows)
ABCDE12	Yes	26-Dec-08
ABCDE12	Yes	26-Dec-08
ABCDE12		26-Dec-08
ABCDE15	Yes	27-Dec-08
ABCDE17	Yes	27-Dec-08
ABCDE17		27-Dec-08
ABCDE18		26-Dec-08
ABCDE19	Yes	29-Dec-08
ABCDE20	Yes	31-Dec-08
ABCDE20		2-Jan-09	Jan data(10 rows)

Re: incremental load

marcus_sommer — Fri, 16 Dec 2016 14:51:52 GMT

Here is an error:

Let vmax = Peek('maxdate',0,'Try');

because you generate maxdate in the table Max and not within Try which meant vmax will be NULL. Change it to:

Let vmax = Peek('maxdate',0,'Max');

and it should work.

- Marcus

Re: incremental load

Anonymous — Sat, 17 Dec 2016 04:35:56 GMT

Thank you Marcus.

Having same concern again even though I'm

Let vmax = Peek('maxdate',0,'Max');

My desired data is not picking up.

I need data with history and if there are any updates I need that line inserted.

ex:

These rows from Jan data to be considered as history not Duplicate rows.

ABCDE12	Yes	26-Dec-08
ABCDE12	Yes	26-Dec-08
ABCDE12		26-Dec-08
ABCDE17	Yes	27-Dec-08
ABCDE17		27-Dec-08

Similarly these rows from feb data should pick up as there is some updation done on previous history

ABCDE16	Yes	1/3/2009
ABCDE16		1/3/2009

Plz help.

Thanks,

Raghav

Re: incremental load

maxgro — Sat, 17 Dec 2016 08:30:44 GMT

Some small change in bold

SET DateFormat='MM/DD/YYYY';

IF NoOfRows('Try') >= 0 then

DROP Table Try;

ENDIF

Let vfirsttime = IsNull(QvdCreateTime('try.qvd'));

If $(vfirsttime) then

trace ********** initial load;

Try:

LOAD ID, ISRejected, DateModified

FROM Book1.xlsx (ooxml, embedded labels, table is Jan);

ELSE

trace ********** incremental;

Max:

LOAD Max(DateModified) as maxdate

FROM try.qvd (qvd);

//Let vmax = Peek('maxdate',0,'Try');

Let vmax = Peek('maxdate',0,'Max');

drop table Max;

trace vmax=$(vmax);

//Incremental load

Try:

LOAD ID, ID as ID2, ISRejected, DateModified

FROM Book1.xlsx (ooxml, embedded labels, table is Feb)

Where DateModified>$(vmax);

Concatenate (Try)

LOAD ID, ISRejected, DateModified

FROM try.qvd (qvd)

where not Exists (ID2, ID);

DROP Field ID2;

ENDIF;

Store Try into 'try.qvd'(qvd);

Store Try into 'try.xls'(txt);

Re: incremental load

Anonymous — Mon, 19 Dec 2016 04:56:01 GMT

Thank you maxgro . It is working now.

Re: incremental load

oknotsen — Mon, 19 Dec 2016 14:34:04 GMT

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).

If not, please make clear what part of this topic you still need help with .

Re: incremental load

Anonymous — Tue, 20 Dec 2016 05:26:05 GMT

Correct Answer