Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
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