topic Re: Make a LOAD optimized - with a >= - plz help in QlikView

Make a LOAD optimized - with a >= - plz help

datanibbler — Thu, 24 Nov 2016 14:21:07 GMT

Hi,

I know the question of optimized vs. non-optimized LOAD has been here a lot of times. I've read up on it a little and if there was a filter for one specific value, it might make sense to load this into a small helper_table and then use a WHERE_EXISTS clause to make the main_LOAD optimized - but here I don't have one specific value: Rather I have a date and I filter a very big table for only the records with a date equal to or later than that - I have the ">=" operator.

=> Is there any way I can make that an optimized LOAD (from a qvd)?

Thanks a lot!

Best regards,

DataNibbler

Re: Make a LOAD optimized - with a >= - plz help

Anonymous — Thu, 24 Nov 2016 14:32:04 GMT

You could generate a table of all the dates that you need to load.

I do this often and it works great.

Re: Make a LOAD optimized - with a >= - plz help

lironbaram — Thu, 24 Nov 2016 14:38:54 GMT

you could create a list of the dates

and then use where exists

to create the list of the dates you could use this script

let vDate = date('01/11/2016');

load '$(vDate)'+IterNo()-1 As Date

AutoGenerate 1

While '$(vDate)'+IterNo()-1 <=today();

Re: Make a LOAD optimized - with a >= - plz help

datanibbler — Fri, 25 Nov 2016 06:54:20 GMT

Hi Liron,

that's great. But then I'd have a LOAD with a WHERE_EXISTS clause with more than one value in it - would that still be optimized?

Hmmm ... yes, I just found a thread that suggests it would be an optimized LOAD. Let's see if it improves the performance.

Re: Make a LOAD optimized - with a >= - plz help

datanibbler — Fri, 25 Nov 2016 07:14:39 GMT

Oh - I just remember, there is a second filter, so I guess it would not be optimized - or, maybe I could make it, but only for the smaller part:

- For one part of that big table, the smaller part, there is a filter for one specific field (TRANCODE) for one specific
value, SPRECEIVE

- For the other part, the filter is for the field having any other value - there are several other possible codes.

Hmmm ... but there is one step before that, the big table from which I load is put together from the archive and the current table in another app that runs once an hour. Maybe there I could create one more field with just a 1/0 value for that field.

Let's see. I'll go step by step.

Re: Make a LOAD optimized - with a >= - plz help

datanibbler — Fri, 25 Nov 2016 08:36:15 GMT

Hmmm ... it's not an optimized LOAD anymore. Strange. There are no compound keys anymore, I have moved that process into the preparatory app that runs once an hour.

Well, then it doesn't improve the performance. Too bad. That one table that has to be prepared is the last "big fish" rgd. the performance of that report - if I could make that any faster, it would improve the whole thing.

Then there is one more thing I can go after - at some point, the nr. of records I have is multiplied - I have many more records in my table than are actually displayed in the straight_table_diagram on the GUI. That also makes the whole thing bigger - and slower - than it need be.

Re: Make a LOAD optimized - with a >= - plz help

datanibbler — Fri, 25 Nov 2016 09:39:16 GMT

Okay,

seems I made it. I now why it happened now, and I fixed it.

The thread is closed herewith.

Best regards,

DataNibbler

P.S.: Sorry - it is not yet. I have changed the code now so there is only one field in the WHERE_EXISTS clause - but I have followed some links on this issue and I think it can only be used with one parameter - true?