Calendar

[datanibbler]

Hi,

I'm currently trying to build some optimization into the DataModel of one report.

This is about the calendar: This is currently being done by extracting the MinDate and MaxDate from a Linktable, which is rather big, and then autogenerating the calendar.

I'm trying to follow the hint from the book "QlikView unlocked" using an Autogenerate with the FieldValue() function to find the Min and Max Dates (no RESIDENT). This is supposedly a lot faster for big tables.

<=> However, there is a catch in this report that makes me wonder whether this is possible:

There is a second RESIDENT LOAD of the same kind, but this time with a WHERE clause. An autogenerate is of course independent of any specific table, so I wonder whether building in a WHERE clause would work?

Otherwise, I could replace just one of the two RESIDENTs which reduces the potential to optimize in this way.

Can anyone give me a hint how to do this? Is there another possibility?

Thanks a lot!

Best regards,

DataNibbler

[marcus_sommer]

Please post the script of the second load and all what is related to them.

- Marcus

[datanibbler]

Hi Marcus,

the second LOAD is like this:

>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Datumswerte:
NoConcatenate
load
min(%DATUM) as MinDatum,
max(%DATUM) as MaxDatum
Resident Linktable
where Quelle = 'Projektliste';

let vMinDatumPL = peek('MinDatum');
let vMaxDatumPL = peek('MaxDatum');

drop Table Datumswerte;

>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

(the table >> Linktable << is made up from several tables and rather big, and the field >> Quelle << is a hardcoded text - I just realize this is only about the last part which isn't quite so big - so if I split the creation of the linktable, which is not particularly pretty in itself, I could apply this same method to that part. Worth a shot.

[fvelascog72]

Hi,

You can try with:

load
min(%DATUM) as MinDatum,
max(%DATUM) as MaxDatum

;

load distinct
%DATUM

Resident Linktable;

Maybe the distinct with a big table last a lot.

Saludos.

[datanibbler]

Thanks Federico!

It is a bit frustrating trying to optimize these reports - when I run them repeatedly, execution_time goes down, then up again for no apparent reason ... there is something else happening on the server, I don't know.

Right now, all this appears kind of superfluous anyway because all the scripts are run only once a day in the early morning, so ten or twenty seconds don't seem to be of much importance. It's only when looking at what is going to come/ might be coming that it makes sense.

[marcus_sommer]

In my opinion it looked not very useful because it would only have a meaning if there are more than one calendar and that they should not contain the same periods (one calendar means that a load over the distinct fieldvalues would be enough and by more than one calendar that there is a consideration of the sources to the dates and therefore a resident with a where-clause). But I'm not sure if this would be really sensible.

If really more as one calendar with different content is needed it would be more performant to load their content before. This meant to load this data directly after the origin tables are loaded because quite often is the link-table one of the last load-steps within the load-chain and even when the order of loads is different they might be changable and if not an appropriate renaming of fields could ensure that a load over the fieldvalues would always work.

This could mean some efforts for changing the order of loadings and might increase the number of statements but it would be always more performant than filtering a huge table with a where-clause and applying the aggregation.

Beside the fact that this kind of calendar-generation over min/max-vaues is quite common doesn't mean that it is good choice at all. Quite often the start-date of the data is already known and mustn't be read from the data - it could just be applied as a fix value. The same is valid to the end-date which is often today(), today()-1 or the month/year-end() of them. Therefore the min/max-approach could be often saved.

- Marcus

[datanibbler]

Hi Marcus,

you're quite right. There is no second calendar, but the secondary min and max dates come from a different table and are used in the final creation of the calendar - I don't yet know the details, I just take things as they are for now.

I realized in other apps there is a hardcoded min and max date for the calendar without any loading. I will eventually ask what is the background to see whether it's necessary.

I went for separating the last part which is supposed to be appended to the LinkTable, seperating it for the time, using QUALIFY to avoid synthetic keys, then using that Fieldvalue()-method to get the min and max dates and finally renaming all the fields and appending it to the linktable as it's supposed to be. It's kind of hard to tell if it really saved me any time, execution time goes up and down for strange reasons ... I believe it has, though.

Best regards,

DataNibbler