Multiple Condition SELECT

Discuss Tips and Techniques for Quantrix. Hosted by Quantrix Professional Services.

Moderator: Moderators

Multiple Condition SELECT

Postby Steven B. » Mon Jan 05, 2009 2:40 pm

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.
You do not have the required permissions to view the files attached to this post.
Steven Bailey
Consulting Manager
Quantrix Professional Services
207-775-0808 x2
sbailey@quantrix.com
Steven B.
Quantrix Forum Member
 
Posts: 37
Joined: Fri Mar 04, 2005 10:51 am
Location: Portland, Maine

Re: Multiple Condition SELECT

Postby RLopez » Fri Mar 03, 2017 4:00 pm

This is fantastic! Thanks for sharing Steve B.
RLopez
Quantrix Forum Member
 
Posts: 51
Joined: Wed Mar 12, 2014 10:38 am


Return to Tips and Techniques

Who is online

Users browsing this forum: No registered users and 1 guest