topic Re: Updating Values in QVD in QlikView

Updating Values in QVD

jyothish8807 — Mon, 01 Jun 2015 11:49:35 GMT

Hi All,

I need to update few fields in my QVD. I don't want to go for incremental load, How can i do it.

Here is the scenario:

ID	Date
1	1/1/2015
2	1/1/2015
3	1/2/2015
4	1/3/2015
5	1/4/2015
6	1/1/2015

Over here, i want to update the date for ID=2,4,5 to 2/2/205,2/3/2015,4/5/2015.

Regards

Re: Updating Values in QVD

swuehl — Mon, 01 Jun 2015 11:51:17 GMT

LOAD the new values first, then your historic data using a WHERE NOT EXISTS(ID) clause.

Re: Updating Values in QVD

Peter_Cammaert — Mon, 01 Jun 2015 11:52:16 GMT

QVD files should be seen as one big inseparable block of data. No changing individual rows, sorry. Go for the classic LOADinRAM-MODIFY-STOREtoDISK approach.

Re: Updating Values in QVD

Colin-Albert — Mon, 01 Jun 2015 11:55:54 GMT

There are third-party utilities that can edit QVD files.

Though use at your own risk on live data!

Reloading, updating and storing the QVD again through Qlik is a safer option.

Re: Updating Values in QVD

jyothish8807 — Mon, 01 Jun 2015 11:57:27 GMT

Sorry cant do this, i need to make changes on the extraction layer and already its very complicated with lots of concatenation and joins.

Regards

Re: Updating Values in QVD

PrashantSangle — Mon, 01 Jun 2015 12:05:12 GMT

Hi,

Using below 2 ways you can do that,

1: In Database itself update it and then at extraction level use normal select query.

2: If for limited ID you want to do that then go for If else

if(id=2,'2/2/2015',if(id=3,'2/3/2015',Date)) as New_date

Note : Carefull with date format.

Regards

Re: Updating Values in QVD

swuehl — Mon, 01 Jun 2015 12:07:13 GMT

What I proposed is an incremental LOAD with update option. You can check the QV cookbook, it's explained in more detail there.

Why can't you do it that way?

DATA:

LOAD ID, Date

FROM NewValues;

LOAD ID, Date

FROM Historic.qvd (qvd)

WHERE NOT EXISTS(ID);

STORE DATA into Historic.qvd (qvd);

Re: Updating Values in QVD

Kushal_Chawda — Mon, 01 Jun 2015 12:07:54 GMT

You can use mapping table to update the date in main table and then store the qvd

MAP:

mapping LOAD * Inline [

ID, Date,

2, 2/2/205

4, 2/3/2015

5, 4/5/2015];

Data:

LOAD *,ApplyMap('MAP',ID, Date) as Update_Date Inline [

ID, Date

1, 1/1/2015

2, 1/1/2015

3, 1/2/2015

4, 1/3/2015

5, 1/4/2015

6, 1/1/2015 ];

STORE Data into Data.qvd;

Re: Updating Values in QVD

jyothish8807 — Mon, 01 Jun 2015 12:10:48 GMT

Hi Peter,

Can you please explain me a bit more.

I have a sheet which i used as initial load, later i updated the QVD by concatenating it with Other data. Now I want to make changes in the historic data with out altering the current data in qvd.

Regards

Re: Updating Values in QVD

jyothish8807 — Mon, 01 Jun 2015 12:55:21 GMT

Thanks Max,

I thought if condition wont work, so never tried . I just missed that.

Regards