topic Incremental Load in QlikView

Incremental Load

varunreddy — Tue, 11 Oct 2016 13:43:16 GMT

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

Re: Incremental Load

sunny_talwar — Tue, 11 Oct 2016 13:46:31 GMT

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

Re: Incremental Load

varunreddy — Tue, 11 Oct 2016 13:55:20 GMT

Yes. It is only loading new records.

Re: Incremental Load

sunny_talwar — Tue, 11 Oct 2016 13:56:40 GMT

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

Re: Incremental Load

varunreddy — Tue, 11 Oct 2016 13:58:54 GMT

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

Re: Incremental Load

sunny_talwar — Tue, 11 Oct 2016 13:59:55 GMT

That is very strange, can you share your script?

Re: Incremental Load

sunny_talwar — Tue, 11 Oct 2016 14:00:13 GMT

And possibly the logfile

Re: Incremental Load

mohitd910 — Tue, 11 Oct 2016 14:01:19 GMT

Does it loading the new records only.

Please check your script on dummy data.

How are you using incremental load ?

Re: Incremental Load

varunreddy — Tue, 11 Oct 2016 14:12:33 GMT

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;

Re: Incremental Load

sunny_talwar — Tue, 11 Oct 2016 14:18:38 GMT

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

Re: Incremental Load

varunreddy — Tue, 11 Oct 2016 14:53:43 GMT

If I change where condition, i am getting syntax error

Re: Incremental Load

sunny_talwar — Tue, 11 Oct 2016 15:05:40 GMT

What is the exact error you are getting?

Re: Incremental Load

varunreddy — Tue, 11 Oct 2016 17:31:55 GMT

Re: Incremental Load

sunny_talwar — Tue, 11 Oct 2016 17:36:11 GMT

Where Clause isn't right, try this:

SQL SELECT *
FROM DQIHKPA1."DQMR_HK01_TBL"

Where SYSTEMDATE > TO_DATE('09/27/2016', 'MM/DD/YYYY');

Re: Incremental Load

varunreddy — Tue, 11 Oct 2016 17:38:03 GMT

This is my where clause:

where Date(SYSTEMDATE,'MM/DD/YYYY') > $(vMaxDate) .

Is there anything I need to correct?

Re: Incremental Load

sunny_talwar — Tue, 11 Oct 2016 17:42:03 GMT

Try this out

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

Re: Incremental Load

varunreddy — Tue, 11 Oct 2016 18:06:19 GMT

It worked. Thanks for your replies.

Cheers,

Varun Reddy. K

Re: Incremental Load

sunny_talwar — Tue, 11 Oct 2016 18:06:46 GMT

No problem

Re: Incremental Load

varunreddy — Tue, 11 Oct 2016 18:07:43 GMT

So the variable was not in the date format?

Re: Incremental Load

sunny_talwar — Tue, 11 Oct 2016 18:09:12 GMT

It is in date format, but not in a format that SQL would take. SQL usually takes in DD-MMM-YYYY format or you can use TO_DATE function for SQL to understand your date as date.