Multiple Condition SELECT

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
1.70K viewsTips and Techniques

Flying Keys
Here is one that is hot off the press. Have you ever wanted to select some data based on multiple conditions? Would you like to write a select statement that went like this, SELECT( a value where a bunch of conditions are equal to my lookup)?

The SELECT function requires that for every value you are looking to return there has to be a one-to-one relationship to the key (or index). As each item is evaluated through the range, Quantrix considers if the key value (or index) is equal to the lookup value. If so, it returns that value or it can be aggregated with a SUM function. However, what if you want to have each value be evaluated against a multitude of potential key values?

Here is the trick. Build your key on the fly. What do I mean by that? As each item in your range is being evaluated by the SELECT function, you can have the key generated at the same time.

In the sample model, you can see the flying key being generated in Formula 1 of the Factors matrix. I actually have four conditions I want to evaluate in the Reference matrix; is the basis greater than the minimum, is the basis less than the maximum, is the iteration greater than the minimum and is the iteration less than the maximum. If the answer to all these questions is yes, than that is the bucket I want. Therefore, I set my lookup value to be “1”. If any of those answers is a “no”, then the flying key is 0.


This is fantastic! Thanks for sharing Steve B.