S...L...O...W..... Min/Max

[Anonymous]

I have the below calculation running against a "telemetry" data set. That data set is about 300 million rows, and all time-series.

LOAD  
               min([CalendarDate]) as minDate,  
               max([CalendarDate]) as maxDate  
Resident telemetry;

Any thoughts on how to get the above query (or alternative) to run in say a couple of minutes or less instead of many hours?

Clarification: the above slowness occurs even if the number of records is limited to say 30M. The last run was, I believe, over 6 hours long.

Message was edited by: Chris Kelley

[#2]

When you load Telemetry Table, you could order by CalendarDate

Then using Peek Function you could get the First and the last rows that It will give you the Max and the Mix values

I hope this is what you are looking for

Cheers

OR

[vincent_ardiet]

Hi Chris,

Thanks to the great Rob Wunderlich and his blog Qlikview Cookbook | Recipes for Qlikview Success, I can provide you a solution, try this:

Load

   min(date(FieldValue('CalendarDate',RecNo()))) as minDate,

   max(date(FieldValue('CalendarDate',RecNo()))) as maxDate

AutoGenerate FieldValueCount('CalendarDate') ;

I haven't test the performance personally but apparently it's a killer's trick.

Regards,

Vincent

[tresesco]

First store the calendar table into a qvd and then instead of taking the data from a resident table, try taking it from qvd.

• LOAD  
•                min([CalendarDate]) as minDate,  
•                max([CalendarDate]) as maxDate  
• From <xxx.qvd(qvd)>;

[vincent_ardiet]

Hi Chris,

Have you tried the different solutions we've proposed to you?

I'm very interesting to know the impact on the performance.

Regards,

Vincent

[rwunderlich]

As Vincent pointed out, the slowness comes from reading all rows of the table vs the faster method of reading only the distinct values. The example on the blog (since corrected) was missing a LOAD statement. Here's the corrected syntax:

Load

  min(CalendarDate) as minDate,

  max(CalendarDate) as maxDate

;

Load

  FieldValue('CalendarDate',RecNo()) as CalendarDate

AutoGenerate FieldValueCount('CalendarDate') ;

I've also published an example qvw in the Cookbook Recipes:

Qlikview Cookbook: Load From FieldValues http://qlikviewcookbook.com/recipes/download-info/load-from-fieldvalues/

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[vincent_ardiet]

Hi Rob,

The one in a single load gives the expected result.

Is it faster with the preceding load?

Regards,

Vincent

[rwunderlich]

Hi Vincent,

It's not faster with the preceding load. But in some cases, I get "?" for results without preceding load. Not sure why, but it always works with the preceding load.

-Rob

[vincent_ardiet]

Ah OK, that's why I have used the Date() function in my code. It avoids the question marks.

Apparently the ? are here because QV cannot identify the format of the data.

[rwunderlich]

Thank you Vincent! I was banging my head against the wall on that one!

-Rob