Join on unequal values due to rounding

[matula23]

I have 2 data sets that I am trying to join. The only 2 similar fields is an account number and a value. However, there can be many of the same account values repeated over and over again so I need to join on the value to get a more accurate join. The problem is that my values do not match exactly. For example, source A has an amount of $70.80 while source B has an amount of $70.81. I tried using the FLOOR formula on each source so I can truncate the amounts to a whole dollar value, but I'll run into a situation where source A value is $639.99 and source B amount s $640.01, so those will fall out of my join even if i truncate to a whole dollar amount. Is there any way that I can reasonably achieve this?

[binuacs]

@matula23 may be you can use the round function and try the join

[matula23]

@binuacs - I tried this solution but it wont work in ever situation, because I have examples where my amounts are $xx.48 and $xx.51. In these examples, one source will round up and one source will round down.

[apathetichell]

@binuacs --- I'd recommend using fixed decimal x.2 vs double --- it makes for easier joins.

@matula23 --- create terms called match term or something. join on that term. for entries unjoined creat new match terms with less specific rounding. join those. see what's leftover. repeat? Create a threshold for success like 99.9% or 99% ---> aim for that.