Join data within a specific percentage

[Carlos77]

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]

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. 

[Carlos77]

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 Number	LC Amount						File Number	LC Amount
20-9076-90530000	12509.09						23-9082-93303300	128929
3028878445-0383	678.87						20-9076-90530000	12509.09
3030387395-0380	-16335.23						3028878445-0383	14889.13
3028909343-0589	-15961.84						3028909343-0589	12673.5
3028909340-0589	15864.31						3028909340-0589	12673.5
23-9082-93303300	128929.53						26-5353-00909900	50000
53-5353-00903300	46376.95						53-5353-00903300	46376.95
60-9073-00904000	61119.14						60-9073-00904000	61119.14
3033809597-0935	11635.65						3030387395-0380	16335.23
26-5353-00909900	50000						94-5350-93003300	290522.62
00-9333-93303700	-849.44						00-9332-93303200	14539.96
23-9082-93303300	128929.53						00-9333-93303700	19204.51
20-9325-93303600	-56873.67						20-9325-93303700	13793
20-9325-93303700	-56483.67						20-9325-93303600	40000
3030387395-0380	16335.23						20-9325-93303600	13793
00-9332-93303200	-96.97						20-9325-93303700	40000
3028878445-0383	678.87						20-9083-93206300	13623.86
3028909343-0589	-15961.84						20-9073-00402600	19420
3028909340-0589	15864.31						48-5350-00402300	1000000
94-5350-93003300	290522.62						3032593375-0047	43843.63
3030387395-0380	16335.23						3030387249-0380	13657.75
94-5350-93003300	-290522.62						20-9073-00502300	40412.38
3028878445-0383	-678.87						20-9073-00502000	14706.13
3028224456-0562	-4301.87						3028224456-0562	13877.01
94-5350-93003300	-290522.62						60-9073-00603300	13100
3028878445-0383	-678.87						94-5350-00603300	219172.26
20-9083-93206300	-13623.86						23-9325-00303800	33976.31
3030387395-0380	16335.23						3033809597-0935	28176.66
20-9083-93206300	-13623.86						3033809597-0935	18691.51
3030387249-0380	-21892.33						3033809597-0935	18688.11
3028224456-0562	4301.87						3033809597-0935	18572.21
48-5350-00402300	1000000						3029845058-0380	18852.72
3032593375-0047	-23.95						3029845058-0380	18347.97
20-9073-00402600	31556.85						3029845058-0380	17874.75
20-9073-00502000	-17150						3029845058-0380	18347.97
20-9073-00502300	140						3029845058-0380	18780
48-5350-00402300	-1000000						3029845058-0380	17878.23
3030387249-0380	-21892.33						3029845058-0380	18792.55
3030387249-0380	-21892.33						3029845058-0380	18852.72
23-9325-00303800	-35025.21						3029845058-0380	17878.29
48-5350-00402300	-1000000						3029845058-0380	23055.43
60-9073-00603300	19820						3029845058-0380	19745.08
60-9073-00603300	350						3029845058-0380	18321.91
94-5350-00603300	-219172.26						3029845058-0380	18795.9
3029845058-0380	21366.64						3029845058-0380	23055.43

[Emil_Kos]

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!