topic Re: Getting Max and Min Date from a Fact table in QlikView

Getting Max and Min Date from a Fact table

sspe — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

When we load our data into Qlikview, we are building a Date dimension based on the first and last date in the Fact table. This is working well, but when the number of records in the fact table starts to grow, it starts to be a little slow to find these two dates.

We are loading the dates into a temp table and then use PEEK to get the values as you see below -

DATE_SUMMARY:

Load

Date(Min(Date_Key)) as Min_Date,

Date(Max(Date_Key)) as Max_Date

Resident [Fact];

/* Assign the Min_Date and Max_Date to a temp variable */

Let vDateMinTemp = Peek('Min_Date',-1, [DATE_SUMMARY]) ;

Let vDateMaxTemp = Peek('Max_Date',-1, [DATE_SUMMARY]);

My question is now if there are any other (and faster) ways to do this?

Regards

Steen

Re: Getting Max and Min Date from a Fact table

sunny_talwar — Thu, 12 May 2016 09:19:41 GMT

May be look into this approach:

“Fastest” Method to Read max(field) From a QVD | Qlikview Cookbook

Re: Getting Max and Min Date from a Fact table

Kushal_Chawda — Thu, 12 May 2016 09:31:50 GMT

DATE_SUMMARY:

Date(Min(Date)) as Min_Date,

Date(Max(Date)) as Max_Date;

LOAD FieldValue('Date_Key', recno()) as Date

autogenerate FieldValueCount('Date_Key');

Let vDateMinTemp = Peek('Min_Date',-1, 'DATE_SUMMARY') ;

Let vDateMaxTemp = Peek('Max_Date',-1, 'DATE_SUMMARY');

Re: Getting Max and Min Date from a Fact table

sspe — Thu, 12 May 2016 13:12:50 GMT

Hi,

Thanks for your feedback.It's basically the same solution both of you suggest and it seems to be quite a bit faster:-).

Regards

Steen