incremental load

[Anonymous]

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)

[marcus_sommer]

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

[Anonymous]

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

[maxgro · Accepted solution]

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

[Anonymous]

Thank you maxgro . It is working now.

[oknotsen]

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 .

[Anonymous]

Correct Answer