Join on unequal values due to rounding
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Labels:
- Developer
- Developer Tools
- Fuzzy Match
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@matula23 may be you can use the round function and try the join
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
