Getting Max and Min Date from a Fact table

[sspe]

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

[sunny_talwar · Accepted solution]

May be look into this approach:

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

[Kushal_Chawda]

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');

[sspe]

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