How to delete records from a qvd which is done incremental load

[#1]

Hi Team,

We have a process which will create report every month .we built incremental load logic and data is storing into a single QVD for every QVD refresh .But some times customer will come back and ask us to delete some records from the input file and want the output in qlikview without the deleted data.Since data is coming from single qvd deleted records will be still showing in qlikview because during first time load it was loaded to the same qvd.

Let me know is there any way of deleting the data from the front end of qlikview.

Thanks and regards

Sreehari Satheesan

[sushil353]

Hi,

If you can store all the deleted records key into a separate xl file of qvd file then you can load that file into your qvw and then while loading data from qvd, you can simply filter out the deleted records in qvw file...

also if you want to remove those deleted records from front end then you can filter out the data loaded form separately deleted record file..

HTH

sushil

[#3]

Hi Sushil,

Thanks for your reply,

Sorry I didn't get your whole logic .So if you store id's which has to be deleted from qvd into an excelfile  how we can delete that records from our qvd?

It will be good if you give with a sample qvw file or script

[jonathandienst]

Hi

First load the Ids to b excluded in a temporary table:

Excludes:

LOAD Id

FROM ExcludeList.xlsx

(ooxml, ...);

... then load the QVD with a where not exists() condition:

Incremental:

LOAD Id,

  ...

FROM Incremental.qvd (qvd)

Where Not Exists(Id);

... finally drop the temp table:

DROP Table Excludes;

HTH

Jonathan

[#5]

Hi ,

I am attaching the sample qvd as well as qvw.

Please let me know how to delete the ID 9 and 10  from qvd  without using where condition because it is impossible to change the code every month.

Thanks

Sreehari

[fvelascog72]

Hi,

You need a condition in a field or a date or something to know which records will be deleted.

[Peter_Cammaert]

A simple trick is to use the primary keys from your data source. The steps are as follows:

1. Load all new records (increment)
2. Add historical records from the existing QVD (including those that may have disappeared in the meantime)
3. Perform an INNER JOIN of the resulting resident table with the primary keys from your data source (all deleted records will disappear)

Nothing more. Loading or using all primary keys from the data source will be a very fast operation (if your data resides in an RDBMS) due to the index(es) that exists on the PK field.

Peter