topic Re: RangeMin() and RangeMax() in QlikView

RangeMin() and RangeMax()

Tue, 03 May 2016 11:28:52 GMT

When and where to use RangeMin() and RangeMax() function?

Re: RangeMin() and RangeMax()

swuehl — Tue, 03 May 2016 12:59:22 GMT

You can use these functions in chart expressions or script. While writing chart expressions or script.

Why not just have a look at the HELP?

Re: RangeMin() and RangeMax()

rubenmarin — Tue, 03 May 2016 13:13:06 GMT

Is usefull when you want to keep the max of two different fields:

Date(RangeMax([Original Due Date], [Negotiated Due Date]))

Or if you want to set a min or max value to an expression (in example for dynamic axis range):

RangeMin(Sum(Profit), -1) // The axis will start at -1 even if all profits are positive, if there are a more negative number, it will take the lower.

Re: RangeMin() and RangeMax()

Peter_Cammaert — Tue, 03 May 2016 13:14:37 GMT

Wherever you would specify all values as a comma-separated list.

Just for the fun of it, let's introduce a visual analogy: vertical and horizontal value specifications.

A single field name will give you access to 1 or more values in a vertical list (e.g. a listbox).
Vertical -> Max(Fieldname). Max() will travel the vertical list of values and decide on which is the largest.
A number of values or containers separated by comma's also specify multiple discrete values but in a horizontal list.
Horizontal -> RangeMax(parm1, parm2, ...). RangeMax() will hop from one parameter to the next in the horizontal list, and pick the one value that is the largest.

Note that you can also use fieldnames in a RangeMax() parameter list, but they will only produce sensible results if they refer to a single value at a time. Like in:

Sum(RangeMax(Total1, Total2, Total2))

where Total1, Total2 and Total3 are all from a single row or from a single set of connected rows in different tables. Otherwise every Total value will return NULL because RangeMax expects a single value for every parameter.

Best,

Peter

[Edit] Sorry, (Range)Max picks the largest values, not the smallest.

Re: RangeMin() and RangeMax()

Tue, 03 May 2016 14:15:30 GMT

Hi, Thanks for prompt response.

Actually, I have come across below script and couldn't able to understand the functionality of RangeMin() and RangeMax()

TempSubIntervals:

LOAD Distinct

StartDate as [ES Start Date],

EndDate as [ES END Date],

IntervalId

Resident RoomDetail;

SubIntervals:

LOAD *, SubFromDate &'|'& SubToDate as SubIntervalID;

LOAD

IntervalId,

date(RangeMax([ES Start Date])) as SubFromDate,

date(RangeMin([ES END Date])) as SubToDate

Resident TempSubIntervals;

Re: RangeMin() and RangeMax()

swuehl — Tue, 03 May 2016 14:32:40 GMT

If you are using RangeMin() / RangeMax() with a single argument, there is no real benefit from using these functions at all (

since the HELP says for RangeMax():

"Returns the highest numeric values found within a range of 1 to N arguments. If no numeric value is found, NULL is returned.")

Hence, if there is only one numeric value in the range, it will return just this value.

Re: RangeMin() and RangeMax()

rubenmarin — Tue, 03 May 2016 14:34:13 GMT

Hi, in that particular case, I don't understand either, I think that's similar to:

SubIntervals:

LOAD *, SubFromDate &'|'& SubToDate as SubIntervalID;

LOAD

IntervalId,

date([ES Start Date]) as SubFromDate,

date([ES END Date]) as SubToDate

Resident TempSubIntervals;

Maybe is trying:

LOAD *, SubFromDate &'|'& SubToDate as SubIntervalID;

LOAD

IntervalId,

date(Max([ES Start Date])) as SubFromDate,

date(Min([ES END Date])) as SubToDate

Resident TempSubIntervals

Group by IntervalId;

Re: RangeMin() and RangeMax()

Chanty4u — Tue, 03 May 2016 15:10:52 GMT

Hi,

rangemin(expr1 [ , expr2, ... exprN ])

Returns the lowest numeric value found within a range 1 to N arguments. If no numeric value is found, NULL is returned.

Examples:

rangemin (1,2,4)	returns 1
rangemin (1,'xyz')	returns 1
rangemin (null( ), 'abc')	returns NULL
rangemin (above(sum(x),-1,3))	returns the lowest of the three results of the sum(x) function evaluated on the row below the current row, the current row and the row above the current row.

rangemax(expr1 [ , expr2, ... exprN ])

Returns the highest numeric values found within a range of 1 to N arguments. If no numeric value is found, NULL is returned.

Examples:

rangemax (1,2,4)	returns 4
rangemax (1,'xyz')	returns 1
rangemax (null( ), 'abc')	returns NULL
rangemax (above(sum(x),-1,3))	returns the highest of the three results of the sum(x) function evaluated on the row below the current row, the current row and the row above the current row.

Re: RangeMin() and RangeMax()

Peter_Cammaert — Tue, 03 May 2016 15:16:41 GMT

So to summarize the previous, the use of RangeXXX() functions in this particular case will translate all non-numeric values to NULL and will leave the numeric values alone.

Ruben clearly shows in hist first example that there are shorter ways to do the same, so the original may not be a code sample worth spending your time on...

Peter