Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Rohit,
PFA. The document helps to give better understanding about incremental load with example.
Regards.
Siva
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
please read the error or you can take snapshot of error and post here so i can help you.
-Nilesh
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...
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
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