Looping through two different data sources

[robertjsimmons]

I have 2 data sources coming into designer, snippets shown below.  I want to compare the diesel price in data sources one and loop it through data source 2 and bring back the surcharge.  So for example record 1 for Jan 02 is $4.584.  That is greater than $4.56 but less than $4.63 so I would want to bring back $.50 to data source 1.  This would be continued for each record in data source 1.  

I tried to use multi-row formula tool but it only accepts one input.  Any ideas without writing code?  Thanks

[robertjsimmons]

Sorry, $4.583 not $4.584.

[rfoster7]

use the multi row tool on your second table to create a lower limit and upper limit value

So DOE lower limit $4.56, DOE upper limit $4.63, surcharge $0.50. So your DOE upper limit will be row+1:DOE Fuel. 

Then you can just join on Table 1 Retail Price is Between DOE lower limit and DOE upper limit. 

You may have to do a >= DOE lower limit and < DOE upper limit  instead of between. 

You included your samples as pictures instead of text, so I can't copy and paste it to show you a physical example. 

[robertjsimmons]

That was fast.  Thanks!  Here are the complete files.

[rfoster7 · Accepted solution]

So a few things. your Home Depot Fuel file already has a "High" column which is the equivalent of upper limit. So you don't need the multirow tool to make it. 

There's also a bunch of values in your DOE file which are either below the lower limit in your lookup table (1.2) or above the upper limit in your lookup table. (6.169). I can guess that you want to use 0 for your surcharge on the lower end. I don't know what you want to do for the upper end. 

Third: So the normal join tool, as you probably have already figured out, doesn't let you do complex joins (like value1 between value2 and value3). There are a number of ways to do that. There is a 3rd party advanced join tool. You can loop it through an R tool where you can write SQL. But the easiest and least elegant solution is just to do an append fields followed by a filter to get the actual data you need. 

The only question is how you want to handle the values above or below your first and last row in your lookup table. I'll make an assumption that you want to do 0 for lower end and the highest surcharge for the top end. So this workflow creates your missing "mininum" and "maximum" row, appends it to your lookup table, then does an append fields/filter join to get your wanted data. 

[robertjsimmons]

That is great.  I apologize for the confusion on the upper fuel limit, i should have explained that part but you did it correctly.  I really appreciate the help on this.  Thanks so much for the help!