How to perform delete through Incremental Reload

[mdmukramali]

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.

[mdmukramali]

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.

[#3]

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]

Dear,

marcsliving.

Thank you for your Help.

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

Thanks,

Mukram.