Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
varunreddy
Creator III
Creator III

Incremental Load

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

1 Solution

Accepted Solutions
sunny_talwar

Try this out

Where SYSTEMDATE > TO_DATE('$(vMaxDate)', 'MM/DD/YYYY');

View solution in original post

19 Replies
sunny_talwar

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

varunreddy
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator II
Creator II

Does it loading the new records only.

Please check your script on dummy data.

How are you using incremental load ?

varunreddy
Creator III
Creator III
Author

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