topic Question: Concatinate a incremental load in QlikView

Question: Concatinate a incremental load

Wed, 25 Nov 2020 16:16:04 GMT

Hello almighty Qlikview guru's,

I am a pretty newbie if it comes to Qlikview and can use some help with my incremental load.

Currently i'm coding a incremental load of my sales lines which with a lot of reading and trial and error works.

It is a bit slow, and was wondering if i can optimize my loading speed any further.

What i have is so far is this code:

Verkooporderregels:

LOAD

DATE([datum-aanmaak]) as [datum-aanmaak]

FROM $(vStoreInitial)Verkooporderregels.QVD (qvd);

Temp2:

LOAD date(max([datum-aanmaak])) as MaxDate2

Resident Verkooporderregels;

LET vStartDate2 = peek('MaxDate2',-1,'Temp2');

DROP Table Verkooporderregels;

Incremental_VKORGL:

LOAD *;

SQL SELECT * FROM PUB."va-211"

WHERE (cdadmin = '02' or cdadmin = '03' or cdadmin = '05') and ("datum-aanmaak" >= '$(vStartDate2)');

Concatenate

LOAD * FROM $(vStoreInitial)Verkooporderregels.QVD (qvd) where not Exists (%KEYORDRGL);

STORE Incremental_VKORGL into $(vStoreIncremental)Verkooporderregels.QVD (Qvd);

DROP Table Incremental_VKORGL;

All order lines have 3 unique values which, when stuck together make a unique master key.

These field are called cdadmin, cdorder & nrordrgl.

cdadmin lets me know which group they come out of, cdorder is the unique order number and nrordrgl lets me know which line it is pointing to.

In my concatenate table i have tried to include the string

I've created a %KEY value in the initial load (Load cdadmin&'-'&cdorder&'-'&nrordrgl as %KEYORDRGL)

And then concatenate as seen in my script above. The concatenate function did have the output needed...

Now i have all the lines needed only it takes up a pretty long time to load..

My initial load is about 24 minutes, and then my incremental load which takes 15 minutes.

The total rows i have now: 7 745 512. is there any way i can optimize my load to make it run faster?

Thanks for you attention!

Re: Question: Concatinate a incremental load

marcus_sommer — Sat, 17 Dec 2016 06:47:33 GMT

I think there are possibilities. One would be only to load those fields which are really needed and not with a wildcard of "*" all fields. Even if you really need all fields somewhere does this mean you must do thisin this and/or within a single task. To distribute such a task will rather not reduce the load-times over all but it could be fit better in certain task-windows.

Beside them there are faster methods to get the max. value from a field instead of doing an aggregation-load over a resident-load, see: “Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook. Whereby in your case I wouldn't do this at all - I would read the max. value from the field after your concatenating and store it within the variable.

- Marcus

Re: Question: Concatinate a incremental load

Wed, 21 Dec 2016 11:18:15 GMT

Hi Marcus,

Thanks for the response.

Using your link I've dramatically improved my loading speed.