topic Re: Return ID at Min/Max Location in QlikView

Return ID at Min/Max Location

Thu, 16 May 2013 20:50:48 GMT

Hi all,

I'd like to create a (straight table) expression that that returns an ID (or some specified field) given the location of a min/max value. For instance, I'm using the Min() function to to find the minimum value in a range on prices, and now I'd like to move (horizontally) within the same row on the data sheet to return a different ID (in my case, a date) at that location.

In simpler terms, if I find that the minimum purchase price of product XYZ was $1, I want to know what the date on which that product was purchased (it might have been purchased at a higher price on a different date).

I cannot simply ad the date field as an expression/dimension because it will return multiple values

ProductID	MinVal	Date@Min
1	$1	?
2	$4
3	$5
4	$2
5	$3

Re: Return ID at Min/Max Location

rustyfishbones — Thu, 16 May 2013 21:02:15 GMT

Did you try this

=DATE(FIRSTSORTEDVALUE(Datefield,-MinVal))

here is how it looks on my App, I have different fields of Course!!

Re: Return ID at Min/Max Location

Fri, 17 May 2013 03:30:41 GMT

That works perfectly!... in most cases. What if the min/max value appears in the data several times on several different dates (the product ID was purchased at the same min value on 3 different dates, for instance). In that case, I'm returning a '-' because there are multiple selections from which to choose. I don't care too much which date it chooses, as long as I have one for reference. Any ideas?

Thanks,

Paul

Re: Return ID at Min/Max Location

rwunderlich — Fri, 17 May 2013 04:57:57 GMT

Add the DISTINCT keyword in the FirstSortedValue() function

FirstSortedValue(DISTINCT ...

-Rob

Re: Return ID at Min/Max Location

rustyfishbones — Fri, 17 May 2013 07:38:43 GMT

See Rob's answer below, can you give out the points please!

Re: Return ID at Min/Max Location

Fri, 17 May 2013 16:28:46 GMT

Hey Rob and Alan,

You guys are geniuses! Thanks for the help - worked like a charm.

On a side note, any idea why it underlines the expression in red when you ad the DISTINCT keyword, even thought it is an acceptable expression ("expression ok" in the expression box)?

-Paul

Re: Return ID at Min/Max Location

rwunderlich — Mon, 20 May 2013 19:00:28 GMT

I think it's just a case of the syntax checker being wrong. There are a few examples of this. Like the "WHEN" prefix which always flags as red:

when A=1 load * from myfile.csv;

-Rob

Re: Return ID at Min/Max Location

Tue, 18 Jun 2013 14:52:32 GMT

Hey guys,

On a similar note, what if I want to use FirstSortedValue to find the location one larger than the minimum (minID+1), or one smaller than the maximum (maxID-1)?

I assume it would be something similar to the above suggestion, but adding a "+1" or "-1" somewhere, but I can't get it to work

=DATE(FIRSTSORTEDVALUE(Datefield,-MinVal+1))