topic Re: update value in qvd file in QlikView

update value in qvd file

omid5ive — Sat, 16 Sep 2017 05:36:54 GMT

i need historical data from my data in database

i used incremental method for store main data in qvd file.

i need that when user change data in database and i reload my dashboard i want to set a flag on qvd file for each record. in first reload i want to set a 1 for flag field on record but when the data change i want to add new record to qvd file and set 0 on flag record for old record

how can i do it?

Re: update value in qvd file

vinieme12 — Sat, 16 Sep 2017 07:59:17 GMT

What if the same record is updated the next week? what value do you want in the flag? still 1!

instead of adding a flag try to use an updateddate or / reloaded date field

Re: update value in qvd file

omid5ive — Sat, 16 Sep 2017 08:27:58 GMT

i want to change the value of flag

the old one change to 0 and new one 1

Re: update value in qvd file

Peter_Cammaert — Sat, 16 Sep 2017 11:16:25 GMT

I get it; you want to keep the entire trail of record changes, but flag the current one (for an easy load).

The problem is that you cannot patch a QVD. You'll have to load-change-rewrite it in its entirety, just to change a single field value. If the table isn't that large, you can rewrite the entire table to keep things simple and efficient. Most incremental load strategies include some sort of rewriting the reference output QVD.

However, if performance is gonna be a problem (you don't mention table sizes), you could try it in another way:

Split your historical records (records with flag = 0) off from your main QVD (records with flag = 1)
Upon the next incremental load, load the increment from your DB and only add the main QVD, except for the records that have a modified version in the increment. Save the "new" main QVD.
The rows from the "old" main QVD that got superseded, are written to a new QVD in the same location as the previously discarded rows. We create a new QVD because of performance reasons (no load-add-write of huge QVD tables)
If you ever need the history of all records, just load all QVDs with superseded rows. Or let another job first combine them into a centralised QVD, before using them in another document.

Note that with this technique, you don't need a Flag field. The location of a record determines whether it is current or not.

Best,

Peter

Re: update value in qvd file

vinieme12 — Sat, 16 Sep 2017 11:17:18 GMT

so basically you only want the latest record to be flagged?

see this thread

Re: latest row per id