Loading max time from resident takes too long

[chrishayes]

I am loading the max time from a resident table and storing it in a variable. For some reason this can take almost 15 minutes. The resident table has 9.4 million rows. Any ideas? Please reference the log file excerpt below:

2015-06-10 14:27:03:

119 fields found:  ...   9,472,310 lines fetched

2015-06-10 14:28:33: 0269  __MAX_REPORTING_TIME:

2015-06-10 14:28:33: 0270 

2015-06-10 14:28:33: 0271  LOAD

2015-06-10 14:28:33: 0272

MAX(REPORTING_TIME) AS MAX_REPORTING_TIME

2015-06-10 14:28:33: 0273  RESIDENT

2015-06-10 14:28:33: 0274

WIP_TW

2015-06-10 14:42:39:

1 fields found: MAX_REPORTING_TIME, 1 lines fetched

2015-06-10 14:42:39: 0277  LET v_max_reporting_time = TIMESTAMP(PEEK('MAX_REPORTING_TIME'))

2015-06-10 14:42:39: 0279  TRACE Max Reporting Time Value ----> 2015-06-10 14:00:00

2015-06-10 14:42:39: 0279  Max Reporting Time Value ----> 2015-06-10 14:00:00

2015-06-10 14:42:39: 0281  DROP TABLE __MAX_REPORTING_TIME

[rwunderlich]

It's much faster to read the FieldValues() array. See Better Calendar Scripts | Qlikview Cookbook for an example of loading from FieldValues().

-Rob

http://masterssummit.com

http://robwunderlich.com

[stabben23 · Accepted solution]

Hi,

instead of doing a max and then a peek, just do a peek and sort your Table, like this:

Table:
LOAD
REPORTING_TIME
RESIDENT

WIP_TW
order by REPORTING_TIME asc

LET v_max_reporting_time = TIMESTAMP(PEEK('REPORTING_TIME',-1,'Table'))

[chrishayes]

Thanks for the suggestions. I added an order by when loading the original table and this worked great. Thanks!