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?

apathetichell

@binu_acs --- 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.