Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Closest Numerical Match

RPM
8 - Asteroid

I have a list of orders in my data with Quantity. Want to do a lookup against tables like these without writing formulas to keep it dynamic. How do I find the closest match or check that it is in range? Find/replace only works for text fields.

 

For example, if my Q in data is 1600 then my price should return as $47. Thank you.

 

Price Qty
83 300
63 500
56 700
52 1000
49 1500
47 2000
45 3000
45 4000
43 50000
41 75000

2 REPLIES 2
NicoleJohnson
ACE Emeritus
ACE Emeritus

Try the following:

 

1. Append Tool: Use the price list as the Source and the list of orders + quantities as the Target input. This will append the price list to each record in the order table.

2. Formula to check if the order quantity is greater than the quantity in each record of the price table.

3. Filter to show only those values that are true (assuming that your price will be determined by the largest number that is equal to or less than the order quantity, so I think in your initial example that would be $49, not $47?)

4. Sample tool to select the Last 1 record, grouping by original order quantity.

 

Let me know if that helps!

 

Cheers,

NJ

RPM
8 - Asteroid

That works. Thank you!

Labels