Discussion
Pegasystems Inc.
NL
Last activity: 13 Nov 2015 7:26 EST
How to do VLOOKUP like lookup in an expression?
Suppose I have a Page List of Customers
Assume the Customer data model is - in pseudo notation - like this:
Customer {
ID : text,
Income : double
}
| ID | Income |
|---|---|
| CE-1 | 200 |
| CE-2 | 500 |
| CE-3 | 150 |
Now I would like to get the ID of the customer with the highest income. So in this example, that would be CE-2.
What PRPC expression would give me this (a la Excel vlookup)?
Thanks!
Otto
Pegasystems
US
There are several ways... if you have it in a page-list, use obj-sort and pick the first record. if you have it in data page which is being sourced from a report, you can also handle it in the report.
Pegasystems Inc.
NL
Hi, thanks for the replies. Unfortunately not a solution yet.
IndexInPageList isn't going to cut it. This is a lookup of a certain value. Indeed, this may be what VLOOKUP does so the title of my post was slightly misleading. What I'm looking for is something that finds the index of max value of a certain property, then returns me another property at that index. Or, just gives me only the index of the max (or min) value and I'll do the lookup with a second function. In R, an expression like this would do it
customers$ID[which.max(customers$Income)]
In Excel, I believe you have to resort to ugliness like finding the max first, then the index of this value.
First sorting then lookup could work but is very inefficient. Sort is O(n.log(n)) at best, while I just need the max (or min), which is O(n). Also, not sure if I can do a sort + lookup inline in one expression (which is what I'm looking for), and it would still be hard to read.
Hi, thanks for the replies. Unfortunately not a solution yet.
IndexInPageList isn't going to cut it. This is a lookup of a certain value. Indeed, this may be what VLOOKUP does so the title of my post was slightly misleading. What I'm looking for is something that finds the index of max value of a certain property, then returns me another property at that index. Or, just gives me only the index of the max (or min) value and I'll do the lookup with a second function. In R, an expression like this would do it
customers$ID[which.max(customers$Income)]
In Excel, I believe you have to resort to ugliness like finding the max first, then the index of this value.
First sorting then lookup could work but is very inefficient. Sort is O(n.log(n)) at best, while I just need the max (or min), which is O(n). Also, not sure if I can do a sort + lookup inline in one expression (which is what I'm looking for), and it would still be hard to read.
Another alternative was to use a strategy so I have a visual way of doing the same. I prioritize by the lookup value, take the top-1, then pick the property of interest. Easier to understand but still as inefficient as sorting first.
So I'm still looking for a solution that is both efficient as well as easy to understand...