topic Re: Removing Sum(if()) (because it's slow as hell) in QlikView

Removing Sum(if()) (because it's slow as hell)

quilldew — Wed, 06 Dec 2017 09:27:57 GMT

Hi Gang,

This one may be a bit confusing so please bear with me.

I have a script, that works, but is slow slow slow and replicated over many straight tables.

I'm looking for a way to move the sums to the load script, but I'm also open to suggestions as to how to speed these up. I can't post a qvw here, because of the sensitive nature of the data, but I'll put up the code I can.

So one of the sums looks like:

=sum(

if($(vRealDateDelayStart)<=floor(WeekEnd(WeekStartDay))

,if($(vRealDateDelayEnd) >= WeekStartDay

,if($(vRealDateDelayEnd) <= floor(WeekEnd(WeekStartDay))

,if($(vRealDateDelayStart) >= WeekStartDay

,($(vRealDateDelayEnd) - $(vRealDateDelayStart)) +1

,($(vRealDateDelayEnd) - WeekStartDay) +1

)

,if($(vRealDateDelayStart) >= WeekStartDay

,(floor(WeekEnd(WeekStartDay)) - $(vRealDateDelayStart)) +1

,(floor(WeekEnd(WeekStartDay)) - WeekStartDay) +1

)

,if(isnull($(vRealDateDelayEnd)) and $(vRealDateDelayStart) <= floor(WeekEnd(WeekStartDay))

,if($(vRealDateDelayStart) < WeekStartDay

,(floor(WeekEnd(WeekStartDay)) - WeekStartDay) +1

,(floor(WeekEnd(WeekStartDay)) - $(vRealDateDelayStart)) +1

)

The variables are start and end dates created from a timestamp.

I'm sure there will be lots of questions. Hit me.

Re: Removing Sum(if()) (because it's slow as hell)

Gysbert_Wassenaar — Wed, 06 Dec 2017 09:41:30 GMT

Looks like you're only comparing stuff with WeekStartDay. It shouldn't be too hard to move those comparisons to the script and create a new field to flag the matching records. What timestamp are the variables created from? From value selected in a field or user input or a value like Today's date?

Re: Removing Sum(if()) (because it's slow as hell)

quilldew — Wed, 06 Dec 2017 09:49:17 GMT

They are created from two timestamps that are pulled in from a qvd and are linked to a 'person' (unique identifier).

I use this expression to show a figure for every week going back to 2012 in a straight table, the weekstart days are currently stored in a mastercalendar on their own.

I also have another straight table, with the same expression, that shows the 'person''s with a period between the vRealDateDelayStart and vRealDateDelayEnd for whichever week is chosen within the first table. Confusing?

Re: Removing Sum(if()) (because it's slow as hell)

Gysbert_Wassenaar — Wed, 06 Dec 2017 10:20:39 GMT

Perhaps you're looking for something like this: Creating Reference Dates for Intervals

Re: Removing Sum(if()) (because it's slow as hell)

quilldew — Wed, 06 Dec 2017 10:55:37 GMT

That does look like it could help. I will take a good look at it.

I've tried moving my above code to the script, and creating a new table using group by, for some reason it is giving me an error that it is expecting a ')' , but when checking the code all the '()' are paired.

Re: Removing Sum(if()) (because it's slow as hell)

quilldew — Mon, 18 Dec 2017 15:17:59 GMT

That was a real big help and information I can use for other projects in future thank you.