topic Re: How to delete records from a qvd which is done incremental load in QlikView

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

Mon, 30 Mar 2015 04:44:04 GMT

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

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

sushil353 — Mon, 30 Mar 2015 04:49:53 GMT

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

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

Mon, 30 Mar 2015 05:08:10 GMT

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

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

jonathandienst — Mon, 30 Mar 2015 05:29:14 GMT

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

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

Mon, 30 Mar 2015 07:31:13 GMT

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

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

fvelascog72 — Mon, 30 Mar 2015 07:40:30 GMT

Hi,

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

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

Peter_Cammaert — Mon, 30 Mar 2015 07:51:55 GMT

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

Load all new records (increment)
Add historical records from the existing QVD (including those that may have disappeared in the meantime)
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