SELECTLIST- My New Favorite Function

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
Answers
Questions
Solved3.15K viewsFormulas and Functions
0

I’m Baaack!!!. Well, I never really left but didn’t post much for a few years now (maybe eight?).

I have been using SELECT and SELECT with Flying Keys (see long ago post) for a long time. Of late, I have had the need to have multiple lookup values. So to have this functionality in Quantrix truly opens some doors.

How it works: If you a familiar with SELECT, you will recognize that the lookup value is only finding matches with the value list/key list pairs. So if the lookup value is A, SELECT will return all the values where the key list is equal to A. What if you wanted to return all the values where the key list is equal to A and B. This is where SELECTLIST comes in. The lookup value is now a list.

In the attached model, I show how that lookup list can have many values. whip the dependency inspector on those answer cells and you can see the full lists.
The practical application for this is creating a model where an item can be configured to have many different components (or items from another category) and then add up all the costs in a single formula.

Test case 4 is pretty cool, ah?

By the way, a SELECTLIST with a single lookup value (test case 1) is the same as SELECT. I just might just use SELECTLIST for all my future SELECTing needs.

Steven W. Bailey
Managing Director
BlackBriar Advisors LLC
3131 McKinney Ave, Suite 600
Dallas, TX 75204
Office: 214.599.8600
Cell : 207.650.8095
sbailey@blackbriaradvisors.com
http://www.blackbriaradvisors.com
http://www.blackbriarfpa.com

Changed status to publish
0

This is a flying key (key/lookup pair built on the fly):

Sum of Rate = sum(selectlist(Matrix2::Rate,Matrix2::Machine&Matrix2::Part,Matrix1::MachineName&Part))

Selected answer as best
0

For reference attached is the model with both examples provided by SteveB and SAU. Thanks again!

0

SAU & SteveB

Your answers are just what I needed. You are Quantrix Masters. THANK YOU!

0

Sorry Rich, missed the part A nuance. Disregard the prior comment.

0

Rich, try:
Sum of Rate = sum(selectlist(Matrix2::Rate,Matrix2::Machine,Matrix1::MachineName))

0

Steve, good to have you back!
Rich, QM’s List() forms a list ONLY from its arguments separated by commas. Therefore, in your formula instead of the list:
[Machine2|A, Machine3|A], – List(Keys) returns the string “Machine2|A,Machine3|A”.
Nevertheless, if your model’s logic and structure are important to you, then try this formula to get result:
Sum of Rate = Sum(SelectList(Matrix2::Rate; Matrix2::MachinePartKey; Matrix1::MachineName & “|” & Part))

Edited answer
0

SteveB,

Thanks for posting on how to use SelectList. It is indeed AWESOME. I can’t seem to make it work with a nested list function in the third argument or lookup_value_list.

In the attached model I am using this formula:

Sum of Rate = sum( selectlist(Matrix2::Rate, Matrix2::MachinePartKey, list(Keys) ))

but Quantrix seems not to treat list(Keys) as a list.

Any thoughts on how to make this happen?

Latest Questions