topic Issues with calendar script in QlikView

Issues with calendar script

psk180590 — Wed, 14 Dec 2016 11:54:38 GMT

Hello All,

In an existing qlikview application i have an calendar script which generates Min, Max and many other objects for various types of categories. But, it consumes very long time(~24 minutes) where as the rest portion consumes ~4 minutes. Does anyone have an idea of a faster calendar script and also tips to generalize calender objects.

TIA!!

Re: Issues with calendar script

sunny_talwar — Wed, 14 Dec 2016 11:56:27 GMT

Just what you need:

Better Calendar Scripts | Qlikview Cookbook

Re: Issues with calendar script

vinieme12 — Wed, 14 Dec 2016 11:59:43 GMT

There is this The Fastest Dynamic Calendar Script (Ever)

But exactly what part is taking the time?

Re: Issues with calendar script

psk180590 — Wed, 14 Dec 2016 12:22:42 GMT

Hi Vineeth,

Below is my Calender Script:

DateListIST:

LOAD

Min(%Key_Date) as MinDate1,

Max(%Key_Date) as MaxDate1

Resident Facts

Where Key = 1;

Join(DateListIST)

LOAD

Min(%Key_Date) as MinDate2,

Max(%Key_Date) as MaxDate2

Resident Facts

Where Key= '2';

Join(DateListIST)

LOAD

Min(%Key_Date) as MinDate3,

Max(%Key_Date) as MaxDate3

Resident Facts

Where Key= '3';

Join(DateListIST)

LOAD

Min(%Key_Date) as MinDate4,

Max(%Key_Date) as MaxDate4

Resident Facts

Where Key= '4';

Join(DateListIST)

LOAD

Min(%Key_Date) as MinDate5,

Max(%Key_Date) as MaxDate5

Resident Facts

Where Key= '5';

Join(DateListIST)

LOAD

Min(%Key_Datum) as MinDate6,

Max(%Key_Datum) as MaxDate6

Resident Facts

Where Key= '6'

When i check the log file each of this is consuming ~4 mins individually.

Re: Issues with calendar script

vinieme12 — Wed, 14 Dec 2016 12:35:09 GMT

Refer the link posted by Sunny and use Field Value instead.

But why are you referring the same field for multiple keys? why don't you use the min and max dates found among all keys?

Re: Issues with calendar script

stabben23 — Wed, 14 Dec 2016 12:49:23 GMT

LOAD distinct
peek('%Key_Datum',0,'Facts') as MinDate1,
Peek('%Key_Datum',-1,'Facts') as MaxDate1
Resident Facts;

Hi, Maybe a peek will make it faster, To use Min and Max in a large dateset will take some time.

You should also do a order by asc on Your Facts table.

Re: Issues with calendar script

psk180590 — Wed, 14 Dec 2016 13:01:48 GMT

I'm fetching the dates from a Fact table which has been concatenated with data from various tables and "Key" in the above script shows which table the data belongs too.

Also, i need to show this tables separately in the dashboard with select able time periods for each.

Re: Issues with calendar script

psk180590 — Wed, 14 Dec 2016 13:02:59 GMT

Thanks Staffan,

I will try this too along with the other suggestions.

Re: Issues with calendar script

vinieme12 — Wed, 14 Dec 2016 13:04:49 GMT

Use Fieldvalue(), Refer link posted by stalwar1‌