How do I return a mapped field when a value is between two amounts?

[NHeise13]

I have a list of sales data with various values and I want to be able to map that field so that if the sales value is between a min and max value it returns a separate value from a table like the below.   I have the underlying sales value in one column, and I want to append a separate column that contains the below "Sales TCV Range" based on whether the sales value falls within the defined ranges.

Min Value      Max Value         Sales TCV Range

1                    100,000               <$0.1m

100,001         250,000                $0.1m - $0.25m

250,001         500,000                $0.25m - $0.5m

etc

[taylor_butler]

Hi Nicole, 

I would recommend using the Append Fields tool to map the Sales TCV Ranges to your data and then you can use the filter tool to identify which ranges your value falls into. See screenshot / attached workflow for reference.

[DataNath · Accepted solution]

@NHeise13 how many TCV Ranges are there? If there's only a few then you could just put this into an if statement as an additional column. If not then you could just append your mapping table and do a simple check using that, filtering out values that aren't in any range. However, if your data set is large to begin with then appending will blow your dataset up to n*m where n is the # of records in your dataset and m is the # of records in your reference table.

Example of an if statement to perform this:

IF [Sales value] > 0 and [Sales Value] < 100001 THEN '<$0.1m' ELSEIF

[Sales value] > 100000 and [Sales Value] < 250001 THEN '$0.1m-$0.25m' ELSEIF

... and so on

The formula may be annoying to set up at first but will certainly be better performance-wise and avoid blowing out your dataset if it is viable.

[IraWatt]

Hey @NHeise13, 

One way of doing this quite efficiently is to use the Dynamic Replace tool:

It generates a formula for all your Min and Max values then works it out. 

Any questions or issues please ask :)
HTH!
Ira