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

rfoster7

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. 

doe example.yxzp