In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Join on unequal values due to rounding

matula23
7 - Meteor

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?

3 REPLIES 3
binuacs
21 - Polaris

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

image.png

matula23
7 - Meteor

@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
19 - Altair

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

Labels
Top Solution Authors