Join data within a specific percentage

Question

Hello there,

I would need to join 2 datasets, with the following rules;

Data 1 column 'File Reference' = Data 2 column 'File Reference'

AND

Data 1 column 'LC Amount' is within +/- 50% of Data 2 column 'LC Amount'.

In other words if Data 1 'LC Amount' is 1000,00CHF and Data 2 'LC Amount' is 1200,00CHF, the records should be included in the joined records.

If Data 1 'LC Amount' is 150,00CHF and Data 2 'LC Amount' is 1200,00CHF, the records should not be joined.

I tried searching the forum but couldn't find anything similar.

Thanks and best regards,

Carlos77

Emil_Kos · Answer

Hi @Carlos77,

I have prepared the workflow for you.

I have joined all the data and filtered out row that shouldn't be merged. I used union to join remaining data.

Please mark this solution if this was helpful!

Join.yxmd

Carlos77 · Answer

Hello @Emil_Kos,

Thanks for your reply. Here's some data, if you could propose a solution that would be great.

Thanks and best regards,

Carlos77

Data 1      Data 2 File NumberLC Amount     File NumberLC Amount20-9076-9053000012509.09     23-9082-933033001289293028878445-0383678.87     20-9076-9053000012509.093030387395-0380-16335.23     3028878445-038314889.133028909343-0589-15961.84     3028909343-058912673.53028909340-058915864.31     3028909340-058912673.523-9082-93303300128929.53     26-5353-009099005000053-5353-0090330046376.95     53-5353-0090330046376.9560-9073-0090400061119.14     60-9073-0090400061119.143033809597-093511635.65     3030387395-038016335.2326-5353-0090990050000     94-5350-93003300290522.6200-9333-93303700-849.44     00-9332-9330320014539.9623-9082-93303300128929.53     00-9333-9330370019204.5120-9325-93303600-56873.67     20-9325-933037001379320-9325-93303700-56483.67     20-9325-93303600400003030387395-038016335.23     20-9325-933036001379300-9332-93303200-96.97     20-9325-93303700400003028878445-0383678.87     20-9083-9320630013623.863028909343-0589-15961.84     20-9073-00402600194203028909340-058915864.31     48-5350-00402300100000094-5350-93003300290522.62     3032593375-004743843.633030387395-038016335.23     3030387249-038013657.7594-5350-93003300-290522.62     20-9073-0050230040412.383028878445-0383-678.87     20-9073-0050200014706.133028224456-0562-4301.87     3028224456-056213877.0194-5350-93003300-290522.62     60-9073-00603300131003028878445-0383-678.87     94-5350-00603300219172.2620-9083-93206300-13623.86     23-9325-0030380033976.313030387395-038016335.23     3033809597-093528176.6620-9083-93206300-13623.86     3033809597-093518691.513030387249-0380-21892.33     3033809597-093518688.113028224456-05624301.87     3033809597-093518572.2148-5350-004023001000000     3029845058-038018852.723032593375-0047-23.95     3029845058-038018347.9720-9073-0040260031556.85     3029845058-038017874.7520-9073-00502000-17150     3029845058-038018347.9720-9073-00502300140     3029845058-03801878048-5350-00402300-1000000     3029845058-038017878.233030387249-0380-21892.33     3029845058-038018792.553030387249-0380-21892.33     3029845058-038018852.7223-9325-00303800-35025.21     3029845058-038017878.2948-5350-00402300-1000000     3029845058-038023055.4360-9073-0060330019820     3029845058-038019745.0860-9073-00603300350     3029845058-038018321.9194-5350-00603300-219172.26     3029845058-038018795.93029845058-038021366.64     3029845058-038023055.43

Emil_Kos · Answer

Hi @Carlos77,

I would suggest join the data as it is and unfiltered positions that you didn't wanted to join. 
I don't think Alteryx will allow you to achieve your task in opposite way.

If this is helpful for you please mark my post as a solution.

If not please provide us with a small portion of the data and I can make a working solution for you.