topic Re: small function like in Excel? in QlikView

small function like in Excel?

Sat, 13 Oct 2012 16:08:08 GMT

How to get something like the "small function" in Excel?

F.e. if you have these numbers:

in Excel, small(num,4) = 3

in Qlikview(11), min(num,4) = 5

Re: small function like in Excel?

Anonymous — Sat, 13 Oct 2012 16:55:22 GMT

Hi,

min function is working like small.

what's your problem.

Re: small function like in Excel?

whiteline — Sat, 13 Oct 2012 18:15:57 GMT

Hi.

There is much more powerful function firstsortedvalue():

firstsortedvalue([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])
returns the first value of expression sorted by corresponding sort-weight when expression is iterated over the chart dimension(s). Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order.

Just use the same field for both expression and sort_weight parameters.

Re: small function like in Excel?

whiteline — Sun, 14 Oct 2012 16:41:20 GMT

Hi.

In Excel there is an order of cells in range.

You could add order as separate field and use it for sorting:

LOAD * INLINE [

A, B, num, order

a1, bb, 1, 1

a2, bb, 2, 2

a3, bb, 3, 3

a4, bb, 3, 4

a5, bb, 3, 5

a6, bb, 5, 6

a7, bb, 5, 7

a8, bb, 6, 8

a9, bb, 7, 9

];

Then just use =firstsortedvalue(num, order, 4)

A bit ugly but haven't got anything more.

Maybe it would be better to ask the real question, not just how to replace the Excel function.

Re: small function like in Excel?

Mon, 15 Oct 2012 07:59:04 GMT

Thanks for the help, I already got it to work.

If I just used firstsortedvalue(num,num,n) then in this case it gives a null value for the 3's and 5's.

The way I got it to work is by using firstsortedvalue(num,num+date/100000000,n)

In this case my dates were formated like yyyymmdd. So this way each record has a unique value, and therefore you don't run into problems when you have values that occur more than once.