Delete the Rows from past QVD based on Condition

[dmohanty]

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:

1. 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).
2. 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).
3. 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:

1. While Incremental Reload, always Ignore/Filter out FLAG = Y (Working Fine)
2. 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):

1. 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. 
2. 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!

[chinmayadash07]

• 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

[ugury5]

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