Incremental Load Help??

[rohitians]

Hello Everyone,

I have a document  where in i want to implement  incremental load depending upon time suppose last reload was at 4.00 pm and now it 5.00 pm i just want to reload new data i,e from 4.00 to 5.00 pm. How this can be achieved please help...i have timestamp as a filed..

Thanks and Regards,

Rohit

[nilesh_gangurde · Accepted solution]

You can change the variables and use this script for your data.

Set vQVDName =ABC;

Set vTableName = ABC;

if isnull(QvdCreateTime('..\Qvds\$(vQVDName).qvd')) then

  $(vQVDName):

  Select * from $(vTableName);

  Store $(vQVDName) into ../Qvds/$(vQVDName).qvd;

  Drop table $(vQVDName);

ELSE

  TEMP:

  LOAD

  MAX(LAST_UPDATE_DATE) as LAST_UPDATE_DATE

  FROM

  ..\Qvds\$(vQVDName).qvd

  (qvd);

  Let vLastUpdateDate = timestamp(peek('LAST_UPDATE_DATE',0,'TEMP'),'DD-MMM-YYYY hh:mm:ss');

  Drop table TEMP;

  $(vQVDName):

  Sql Select * from $(vTableName) where LAST_UPDATE_DATE >

    to_date('$(vLastUpdateDate)','DD-MON-RRRR HH24:MI:SS');

  if NoOfRows('$(vQVDName)') > 0 then

  Concatenate

  LOAD *

  FROM

  ..\Qvds\$(vQVDName).qvd

  (qvd);

  Store $(vQVDName) into ../Qvds/$(vQVDName).qvd;

  Drop table $(vQVDName);

  ENDIF

ENDIF

[Siva_Sankar]

Rohit,

PFA. The document helps to give better understanding about incremental load with example.

Regards.

Siva

[rohitians]

Hey,

The code works fine,

but shows error at line,

$(vQVDName):

  Sql Select * from $(vTableName) where LAST_UPDATE_DATE >

    to_date('$(vLastUpdateDate)','DD-MON-RRRR HH24:MI:SS');

to_date is here error occurs..

Thanks and Regards,

Rohit

[nilesh_gangurde]

please read the error or you can take snapshot of error and post here so i can help you.

-Nilesh

[rohitians]

Hey,

THis line is having error

LAST_UPDATE_DATE >

    to_date('$(vLastUpdateDate)','DD-MON-RRRR HH24:MI:SS');

where in we compare the timestamp..

Thanks...

[nilesh_gangurde]

there are following chances of error.

1. Your Filedname is "Timestamp". hence cant use "LAST_UPDATE_DATE" Fieldname as Field to compare.

2. Check the variable ($(vLastUpdateDate))is getting updated properly or not.

3. Also the formate of the date in the variable should be timestamp.

hope this will help.

-Nilesh

[rohitians]

I got it i Did it in following way,

where timestamp(peek('Timestamp',0,'TEMP'),'DD-MMM-YYYY hh:mm:ss') >='$(vLastUpdateDate)';

Thanks for Helping..:d