update records in history QVD. max speed needed

[amien]

I have 2 QVD files.

t1.qvd contains the history table

t2.qvd contains the fresh mutations

what i want to do is to remove the key records which are in t1 and concatenate t2 and then store it as t1.qvd

I need to run this proces several times a hour

i'v tested:

I Where Exists

t3:

LOAD *

RESIDENT t2;

Concatenate (t3)

LOAD

RESIDENT t1

Where Not Exists (%Key);

II ApplyMap

map:

MAPPING LOAD

%Key,1

RESIDENT t2;

t3:

LOAD

*

RESIDENT t1

WHERE APPLYMAP ('map',%Key,NULL()) <> 1;

Concatenate (t3)

LOAD *

RESIDENT t2;

III Keep

t1:

LOAD *

RESIDENT t1;

t2:

LOAD *, %Key AS %Key2 FROM t2;

tmpTable:

RIGHT KEEP (t1)

LOAD

   %Key

RESIDENT t1

WHERE NOT EXISTS (%Key2, %Key);

DROP TABLE tmpTable;

IV Join

t3:

Load *

RESIDENT t1;

LEFT JOIN (t3)

LOAD

%Key,1   As Remove

RESIDENT t2;

t4:

LOAD *

RESIDENT t3

Where Remove <> 1;

Concatenate (t4)

LOAD *

RESIDENT t2;

I is slow. II , III, IV are about the same and much quicker then I.

Anyone have a difference approach?

[swuehl]

Amien,

have you considered loading your historic table from QVD file instead of a RESIDENT LOAD, trying to keep an optimized QVD LOAD?

This might bring  1.  back into the game.

Regards,

Stefan

[maxgro]

2 ideas:

- Load from qvd instead of resident (imho is usually faster)

- If possible, split the qvd (t1 and t2 by month or something else) so you have to only merge some of the t1/t2 qvd; this could be more trickier than option 1

[Gysbert_Wassenaar]

Consider using the Qlikview Components script library. It has optimized functions to make incremental loading both easy to set up and fast. See this blog post for more information: Incremental Load using Qlikview Components | Qlikview Cookbook

[Peter_Cammaert]

How about something like this (assuming a single field Primary Key, which is pretty rare on average):

Increment:

LOAD * FROM I2.QVD (qvd); // Optimized load - very fast

MapKeys:

MAPPING LOAD %Key, 1 AS Flag RESIDENT Increment;

History:

NOCONCATENATE

LOAD * FROM I1.QVD (QVD); // Optimized load - very fast

CONCATENATE (Increment)

LOAD * RESIDENT History

WHERE IsNull(ApplyMap('MapKeys', %Key, NULL()));

STORE I2 INTO I1.QVD (QVD);

DROP Tables I2, History;

Best,

Peter