Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chkelley
New Contributor III

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

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

10 Replies
Not applicable

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

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
Contributor III

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

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

MVP
MVP

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

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
Contributor III

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

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

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

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
Contributor III

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

Hi Rob,

The one in a single load gives the expected result.

Is it faster with the preceding load?

Regards,

Vincent

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

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
Contributor III

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

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.

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

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

-Rob