topic Re: How to perform delete through Incremental Reload in QlikView

How to perform delete through Incremental Reload

mdmukramali — Tue, 01 May 2012 13:42:43 GMT

Dear,

Community.

I am trying to perform Incremental Reload using the following method

Step 1: Generating a QVD for the data from the DB.

Step 2: Fetching the updated data (using a where condition) and concatenating this data with the stored QVD

I am using a PKey (eg: ItemID) and date fields (eg: Creation Date and Modified Date)

Condition : not exists or <>

Step 3: Left Joining the result set with the QVD data to delete any records that were removed from the source DB.

The problem is I can do insert and update but delete is not being performed.

Re: How to perform delete through Incremental Reload

mdmukramali — Tue, 01 May 2012 19:40:11 GMT

Dear,

Community Members.

I need to Perform Incremetnal Reload.

till now what i did , the follwing Steps,

1) I fetch the data from Sql server Database.

2) My Database Table Contain

ItemID(Primary Key),

ItemDescription,

ItemPrice,

CreationDate,

ModificationDate,

CreatedBy

fields.

3) Then i created a Qvd file.(Item.qvd)

4) I Uncommented That Qvd file.(//)

5) i created a new Table by Resident (Item.qvd).

6) the new Table contains Max(CreationDate) as MaxCreationDate,

Max(ModificationDate) as MaxModificationDate.

7) then I stored those two fileds into Variables by using Peek Function.

7) then i loaded the table again from DB by applying the following Where Condition

where ModificationDate>'$(MaxModificationDate)' or CreationDate>'$(MaxCreationDate)

😎 The Result table i Concatenate with Item.qvd file by applying where condition (where NOT EXISTS(ItemID);

9) Then I stored The Result Table into the Item.QVD;

10) by the conditions what i applied i', able to get New Records As well as Updated Records From the Database.

BUT when i delete a Record From Database Table it's Not going to be deleted from My QVD file.

So I need When ever AnyRecord is Delted from DB Table It should by Reflected in my QVW application .

So please Somebody Can help me Out.

Thanks,
Mukram.

Re: How to perform delete through Incremental Reload

Tue, 01 May 2012 21:16:07 GMT

Use a inner join from the database table using a key field. Usually the key field is indexed so it performs fast.

The inner join will remove all rows from the QVD if they do no exist in the database table.

Let ThisExecTime= (today());

Order1:

SQL SELECT

company + cast(ordernum as varchar) as primarykeyorder,

company,

custnum,

orderdate,

shpconnum,

salesreplist,

openorder,

orderamt,

changedate,

ponum,

shiptonum,

ordernum,

requestdate

FROM epicor904.dbo.orderhed

where ((changedate) >= $(LastExecTime) AND (changedate) < $(ThisExecTime))

;

Concatenate LOAD

company,

primarykeyorder,

custnum,

orderdate,

shpconnum,

salesreplist,

openorder,

orderamt,

changedate,

ponum,

shiptonum,

ordernum,

requestdate FROM ... (qvd)

WHERE NOT EXISTS(primarykeyorder);

Inner Join SQL SELECT company,ordernum FROM epicor904.dbo.orderhed;

If ScriptErrorCount = 0 then

STORE Order1 into.....;

Let LastExecTime = ThisExecTime;

End If

Re: How to perform delete through Incremental Reload

mdmukramali — Wed, 02 May 2012 10:12:59 GMT

Dear,

marcsliving.

Thank you for your Help.

I solve my Issue with Your Reply and With Reference Manual.

Thanks,

Mukram.