topic Re: Efficiency Gain in QlikView

Efficiency Gain

samuel_brierley — Fri, 25 Sep 2015 14:40:07 GMT

Hi all,

i was recently introduced to the wonders of incremental load and as far as i know my method is pretty standard, i.e I set a variable to be the last modified date and query the database on that, the problem is the database has multiple date fields that could be the last modified date.

that isnt a problem for my load script but when im assigning the variable what im having to do is create 1 table with one field via the below method, surely there is a more efficient way of doing this?

load

max(Reported_Time) as last_Updated_Date

Resident [Fault List];

load

max(Attendance_Time) as last_Updated_Date

Resident [Fault List];

load

max(Clearance_Time) as last_Updated_Date

Resident [Fault List];

I then take the max of the resulting table.

There's 15 of these in total which is why it takes a long time.

thanks for any help guys

Re: Efficiency Gain

Anonymous — Fri, 25 Sep 2015 14:49:57 GMT

How about :

At the start of your script put the then current datetime into a variable
At the end of your script store this datetime variable value into a qvd
- [You can also put other usefull things into the qvd so you have an historical run log that can be useful]

Then on the next run

Get the max value of datetime from the qvd

Your qvd will be tiddly and it'll get the max quick.

I presume you'll be using last run datetime start in a WHERE clause against the database and using it against all your database's multiple date fields should work fine.

Re: Efficiency Gain

malini_qlikview — Fri, 25 Sep 2015 14:50:57 GMT

You can try this,

Temp:

Load

Reported_Time as last_Updated_Date

Resident [Fault List];

load

Attendance_Time as last_Updated_Date

Resident [Fault List];

load

Clearance_Time as last_Updated_Date

Resident [Fault List];

and finally

Load

max(last_Updated_Date) as last_Updated_Date

Resident Temp;

Drop Table Temp;

in this way you hit max function only once and the load may be faster.

Re: Efficiency Gain

samuel_brierley — Fri, 25 Sep 2015 14:53:26 GMT

OMG so simple when you say it like that!! i apologise for being a complete Pleb.

thanks

Re: Efficiency Gain

Anonymous — Fri, 25 Sep 2015 14:55:16 GMT

No worries, it is something I learnt a while back from stevedark‌ when I was being a complete Pleb.