topic Re: How do you retrieve field2 associated with max(field1) in QlikView

How do you retrieve field2 associated with max(field1)

triciagdaly — Thu, 08 May 2014 14:15:41 GMT

I have an input box field with a date value, and deriving a maximum CP_EFFECTIVE_DATE where the CP_EFFECTIVE_DATE <= date from the input box. The problem is I'm unable to retrieve field (CP_AMOUNT) that is associated with the max(CP_EFFECTIVE_DATE).

Example:

Input box is vBenchmarkDate

In the Expression for CP_EFFECTIVE_DATE, I have:

expression 1: max({<CP_EFFECTIVE_DATE={"<=$(=date(vBenchmarkDate))"}>}CP_EFFECTIVE_DATE)

The date in vBenchmarkDate is 10/1/2012, and it is correctly pulling the date 8/24/2012, which is the maximum CP_EFFECTIVE_DATE that is <= vBenchmarkDate of 10/1/2012. in the table, this date has a CP_AMOUNT of .036 associated with it but am having problems retrieving this value.

I have tried setting an expression on CP_AMOUNT with:

expression 2: firstsortedvalue(CP_AMOUNT,CP_EFFECTIVE_DATE), but it's returning the max(CP_AMOUNT) in the table, and not the one associated with the CP_EFFECTIVE_DATE from expression 1.

Any help would greatly be appreciated.

Tricia

Re: How do you retrieve field2 associated with max(field1)

swuehl — Thu, 08 May 2014 14:34:39 GMT

try

=firstsortedvalue({<CP_EFFECTIVE_DATE={"<=$(=date(vBenchmarkDate))"}>} CP_AMOUNT, -CP_EFFECTIVE_DATE)

Note also the minus befor CP_EFFECTIVE_DATE to get the max.

Re: How do you retrieve field2 associated with max(field1)

triciagdaly — Thu, 08 May 2014 14:39:13 GMT

That works!!! Thank you so much!!!

Re: How do you retrieve field2 associated with max(field1)

triciagdaly — Thu, 08 May 2014 14:41:26 GMT

Will you explain exactly how this is interpreted and why it works? Just curious...

Re: How do you retrieve field2 associated with max(field1)

swuehl — Thu, 08 May 2014 14:56:48 GMT

Consider having a table in your data model with two fields, CP_AMOUNT and CP_EFFECTIVE_DATE.

The expression above will filter your records using the set expression (filtering CP_EFFECTIVE_DATE before vBenchmarkDate), then the function will sort the remaining records based on the second argument ascending and return the value of first argument's expression (field CP_AMOUNT) for the first sorted record.

If you don't use a minus before CP_EFFECTIVE, you will get CP_AMOUNT for the smallest CP_EFFECTIVE, but for the largest when using a minus.

Just create a small table on paper and simulate to see what I mean.

Hope this helps,

Stefan