Lookup Value in Matrix Range

atperry30

Hello,

I need assistance on how to best structure a workflow that needs to capture value stored in a matrix.

Essentially, based on two different performance metrics, I need a "score" returned. An example of the matrix is below.

Metric 1

	10-15%	15-20%	20-25%	25-30%
5-10%	1	1.5	2	3
10-15%	1.5	2	3	4
15-20%	2	3	4	5
20-5%	3	4	5	6

So someone who scored a 10.3% on metric one and a 15.7% on metric two would get a score of 2. 

I know I could write a complicated if/then statement, but I have 6 unique matrices depending on what department a person works at, so it would be pretty exhausting to write out.

Also, I'm not sure if I could use a generate rows as the data is continuous, but maybe that is an option?

I can't share the actual data, but the score each record is a single person and the two scores are two columns.

Input:

Name	Metric 1	Metric 2
Jane Doe	10.7%	15.3%

Desired Output:

Name	Metric 1	Metric 2	Score
Jane Doe	10.7%	15.3%	2

Thank you!

JoeS

Hi @atperry30 

Assuming you don't have hundreds of thousands of records.

This workflow should work well for you:

I create the combinations of the matrix with upper and lower bands in the top.

Append all the people to the matrix, then filter out for the correct result.

As mentioned though, the append fields tool will not be the best if you do have large data volumes. That being said, Alteryx will churn through the data to make it work if you have the PC resources.

Let me know if you need a more efficient workflow and I'll see if I can come up with one.

MetricScore.yxmd