Delete data from table where exists in the load

[davyqliks]

Hi,

I have a situation where i need to delete rows from a QVD if the value exists in the latest load data.

The plan is to remove all rows of an invoice number from the qvd if that invoice number exists in the latest loaded data.

I will then load the latest data again and concatenate with the existing qvd (with the lines removed).

I would like to know how to delete/remove from existing data where exists/match to an invoice no in the load data

Any help on this would be much appreciated.

Thanks

Daniel

[marcus_sommer]

Maybe something in this way:

t: load * from Current; concatenate(t) load * from Historical.qvd (qvd) where not exists ([Invoice Number]);

- Marcus

[davyqliks]

Hi,

Thank you for the input. 

I have been trying to implement the Where not exists statement but have found it removes all but a single line for an invoice.

do you have any idea on this?

Thanks again

Daniel

[marcus_sommer]

If your current data didn't contain the complete invoices else just a few changed lines you couldn't  only use the invoice number else you need also any unique line-ID.

- Marcus

[davyqliks]

Hi,

Thanks again, all invoice data in the initial load was in the 2nd load from which i am trying to remove the invoice leaving the single line in the table.

I have implemented a URN using line number and invoice number although ironically been informed that the line number is not fixed until the invoiced are posted meaning if day has 3 lines and on day 2 line 2 is removed, line 3 becomes line 2.

This prompted me to have to find another work around hence trying to remove the whole invoice from the existing table if invoice number existed in the new daily data and then load the new data and concat with the existing.

Thanks for your help, i will keep trying to locate a solution.

Daniel