Delete the Rows from past QVD based on Condition

dmohanty — Thu, 16 Sep 2021 18:55:10 GMT

Hi Team,

I have a Dataset like this below which gets stored into QVD.

INVOICE_NUM	INVOICE_LINE_NUM	DAY	FLAG	ROW_ADD_STP	ROW_UPDATE_STP
1001	11	MON	N	9/20/2021	9/20/2021
1002	22	MON	N	9/20/2021	9/20/2021

Now the next day/daily load, the Incremental Reload should ONLY fetch the Updated Records from the Dataset and gets Concatenated/Appended (using Not Exists criteria to avoid duplicate) to the yesterday's QVD.
Suppose the next day, the Dataset looks like this below:

The 1st row FLAG gets updated to Y and ROW_UPDATE_STP updates to next date. Because of FLAG = Y, and new 4th row is added with same INVOICE_NUM (1001), with FLAG = N and a new INVOICE_LINE_NUM (111).
The 2nd row FLAG gets updated to Y and ROW_UPDATE_STP updates to next date. Because of FLAG = Y, and new 5th row is added with same INVOICE_NUM (1002), with FLAG = N and a new INVOICE_LINE_NUM (222).
The 3rd row is added just as a brand new Row (No Update).

INVOICE_NUM	INVOICE_LINE_NUM	DAY	FLAG	ROW_ADD_STP	ROW_UPDATE_STP
1001	11	MON	Y	9/20/2021	9/21/2021
1002	22	MON	Y	9/20/2021	9/21/2021
1003	33	TUE	N	9/21/2021	9/21/2021
1001	111	TUE	N	9/21/2021	9/21/2021
1002	222	TUE	N	9/21/2021	9/21/2021

Requirement:

While Incremental Reload, always Ignore/Filter out FLAG = Y (Working Fine)
Extract the Records with Updated or Added rows. In this case last 3 Rows to be extracted and Appended to yesterday's QVD (Working Fine).

Challenge (How to Delete the Past Records from QVD whose FLAG got changed from N to Y in Dataset):

After concatenating, the Past Records which were having a FLAG = N yesterday in the QVD, and today in the Dataset got changed to FLAG = Y, have to be DELETED. In this case, the 1st two entries from 1st Dataset/Top Table have to be deleted from QVD.
The Updated QVD should ONLY look like:

INVOICE_NUM	INVOICE_LINE_NUM	DAY	FLAG	ROW_ADD_STP	ROW_UPDATE_STP
1003	33	TUE	N	9/21/2021	9/21/2021
1001	111	TUE	N	9/21/2021	9/21/2021
1002	222	TUE	N	9/21/2021	9/21/2021

Please suggest ways to Delete the updated Records from the Past/Historical QVD.

Regards!

Re: Delete the Rows from past QVD based on Condition

chinmayadash07 — Thu, 16 Sep 2021 19:09:56 GMT

U can achieve the same using inner join ,while u will write the incremental load.create a composite key by combining invoicenum and invoicelinenumber which is a unque identifiers

Inner join (your table name)

Sql select invoicenum,invoicelinenumber from table name:

Store table into your path\qvdname.qvd

Re: Delete the Rows from past QVD based on Condition

ugury5 — Sun, 12 Feb 2023 16:01:32 GMT

I have similar issue like this. How can i delete updated past records after i load updated records in incremental load method?

topic Re: Delete the Rows from past QVD based on Condition in App Development

Delete the Rows from past QVD based on Condition

Re: Delete the Rows from past QVD based on Condition

Re: Delete the Rows from past QVD based on Condition