topic Re: Delete in incremental load in QlikView

Delete in incremental load

deepakqlikview_123 — Wed, 26 Mar 2014 16:54:34 GMT

Hello All,

Please find below code,

Let ThisExecTime=ReloadTime();

SQL Select CustomerID,

EmpId,

OrderDate

From Orders

Where OrderDate>=date()-1

And OrderDate<$CurrentExecTime;

Concatenate

Load

CustomerID,

EmpId,

OrderDate

From(Filepath)

Where Not Exists(CustomerID);

Inner join SQL Select CustomerID From Orders;

If ScriptErrorCount = 0Then

Store A into(Filename)

Let LastExecTime = ThisExecTime;

End If;

Can you please tell me how code will work moreover I am not able to understand how delete will work.

Re: Delete in incremental load

deepakqlikview_123 — Wed, 09 Apr 2014 05:08:25 GMT

Hello All,

Can you please help me out on this.

Thanks,

Deepak

Re: Delete in incremental load

Wed, 09 Apr 2014 05:11:51 GMT

When you do an inner join, the resultant will get rid of non existing rows of the either tables.

This way you are deleting the unnecessary rows.

Suraj

Re: Delete in incremental load

tresB — Wed, 09 Apr 2014 05:18:06 GMT

Inner Join is playing the role of deletion. Say, that you had CustomerID 1,2 and 3 in the loaded data/qvd. Now in your source data CustomerID 2 is not there anymore. Then with inner join, the common CustomerIDs are 1 and 3, hence only these(1,3) are taken into consideration and 2 get's out of scope(deleted from qvd). Does this help?

Re: Delete in incremental load

Wed, 09 Apr 2014 05:19:24 GMT

Step1:

SQL Select CustomerID,

EmpId,

OrderDate

From Orders

Where OrderDate>=date()-1

And OrderDate<$CurrentExecTime;

Step2:

Concatenate

Load

CustomerID,

EmpId,

OrderDate

From(Filepath)

Where Not Exists(CustomerID);

Step 3:

Inner join SQL Select CustomerID From Orders;

If ScriptErrorCount = 0Then

Store A into(Filename)

Let LastExecTime = ThisExecTime;

End If;

Lets say the script is divided into 3 steps:

Step1: This step pulls all the data from the database which is greater than a particular date and less than the current time(excluding future orders)

Step 2: This already existing qvd or any other file concatenates to the above loaded data and give you a resultant table of history (coming from this qvd) and new data coming from step 1.

Step 3: When you do a Inner join there might be some data deleted from the Database when using that table for inner join, it will ignore the data which was loaded in the past and are not currently in the Database. (this step deletes the data which is deleted from Database)

Hope this helps.

Suraj