Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mdmukramali
Specialist III
Specialist III

How to perform delete through Incremental Reload

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.

3 Replies
mdmukramali
Specialist III
Specialist III
Author

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.

Not applicable

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

mdmukramali
Specialist III
Specialist III
Author

Dear,

marcsliving.

Thank you for your Help.

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

Thanks,

Mukram.