Incremental Load

[varunreddy]

Hi All,

I have 20 million records in a table. I tried to extract the table and store the data in a QVD (It took me 13 hours to load the data). Going forward, I don't want to load the existing 20 million records. I just want to run new records and append the records with existing records. So I tried to perform incremental load. This is only loading the new records. The issue now is after loading the new records, the reload process is still running for 13 hours. I am not sure why. Does anyone come across this situation?

Please help me in understanding the process.

Thanks in advance!

Cheers,

Varun Reddy

[sunny_talwar]

But the number of records pulled from the database reduced which doing incremental reload?

[varunreddy]

Yes. It is only loading new records.

[sunny_talwar]

Does it include a lot of joins or is this a straight pull from a table or view?

[varunreddy]

Straight pull.

I am trying to load QVD. store maximum date in a variable. Dropping the QVD and loading the table from database where date>maxdate

[sunny_talwar]

That is very strange, can you share your script?

[sunny_talwar]

And possibly the logfile

[mohitd910]

Does it loading the new records only.

Please check your script on dummy data.

How are you using incremental load ?

[varunreddy]

Record_HK:

LOAD [IA Project Name],
[Record ID],
[ID Type],
[GB/GF Name],
Country,
[Legal Entity],
[DQ Rule Name],
RULEMETORNOTMET,
[Observation/Report Date],
[User Field1],
[User Field2],
[User Field3],
[User Field4],
[User Field5],
[User Field6],
[User Field7],
[User Field8],
[User Field9],
[User Field10]
FROM

(qvd);


Datemax:
Load
max(date([Observation/Report Date],'MM/DD/YYYY')) as MaxDate
Resident Record_HK;

Let vMaxDate = Peek('MaxDate',0,Datemax);

TRACE $(vMaxDate);

Drop tables Datemax, Record_HK;

NoConcatenate

Record_HK_Update:

LOAD "IA_PROJECT_NAME" as [IA Project Name],
"RECORD_ID" as [Record ID],
"ID_TYPE" as [ID Type],
"LOB" as [GB/GF Name],
COUNTRY as Country,
"LEGAL_ENTITY" as [Legal Entity],
RULEEXECUTABLENAME as [DQ Rule Name],
RULEMETORNOTMET,
Date(SYSTEMDATE,'MM/DD/YYYY') as [Observation/Report Date],
"USER_FIELD1" as [User Field1],
"USER_FIELD2" as [User Field2],
"USER_FIELD3" as [User Field3],
"USER_FIELD4" as [User Field4],
"USER_FIELD5" as [User Field5],
"USER_FIELD6" as [User Field6],
"USER_FIELD7" as [User Field7],
"USER_FIELD8" as [User Field8],
"USER_FIELD9" as [User Field9],
"USER_FIELD10" as [User Field10] where Date(SYSTEMDATE,'MM/DD/YYYY') > '$(vMaxDate)';
SQL SELECT *
FROM DQIHKPA1."DQMR_HK01_TBL";

store Record_HK_Update into (QVD);

Drop tables Record_HK_Update;

[sunny_talwar]

You are doing your Where clause in QlikView. This means that you are still pulling all the records from SQL before restricting them in QlikView. Instead of this, you need to restrict them in SQL

Record_HK_Update:

LOAD "IA_PROJECT_NAME" as [IA Project Name],
"RECORD_ID" as [Record ID],
"ID_TYPE" as [ID Type],
"LOB" as [GB/GF Name],
COUNTRY as Country,
"LEGAL_ENTITY" as [Legal Entity],
RULEEXECUTABLENAME as [DQ Rule Name],
RULEMETORNOTMET,
Date(SYSTEMDATE,'MM/DD/YYYY') as [Observation/Report Date],
"USER_FIELD1" as [User Field1],
"USER_FIELD2" as [User Field2],
"USER_FIELD3" as [User Field3],
"USER_FIELD4" as [User Field4],
"USER_FIELD5" as [User Field5],
"USER_FIELD6" as [User Field6],
"USER_FIELD7" as [User Field7],
"USER_FIELD8" as [User Field8],
"USER_FIELD9" as [User Field9],
"USER_FIELD10" as [User Field10];

where Date(SYSTEMDATE,'MM/DD/YYYY') > '$(vMaxDate)';
SQL SELECT *
FROM DQIHKPA1."DQMR_HK01_TBL"

Where SYSTEMDATE ......;