1 to Many Matching on Amounts

[J-Riedel]

I feel like this might be a long shot, but thought I would post my question out to the community to see if there are others that are smarter than me!

I have 2 tables of data, where I need to match on the amounts only and no other data is common between the 2 tables to use for matching/joining.  Table 1 will always have one 1 amount, but table 2 could have many amounts.  So looking to see if there is a way to join/match on the 1 values in Table #1 to the sum of multiple values in Table #2.

I have attached both my workflow, along with the data to show the relationship between the tables. Here are my issues:

1. I was planning to match off the 1:1 items using the Join tool.  This works great, but for items where there are multiple entries (i.e. the value of $40,000 twice in both tables), I am getting 4 records in my J anchor of the Join.  So looks like AYX is showing all possibilities (2X2=4 records).  How can I prevent this?
2. Any thoughts on how I can join/match of the L and R anchors of the Join in a 1:Many match/join? 
   1. I do know that their could still be items in the L and R that do not join/match at the end.  These are the values that I am trying to filter down to as these are the items that will need to be researched.

Thanks in advance for any and all help!

[TimN]

Hi,

maybe something like this?  

[J-Riedel]

Thank you Tim.  This works great to get rid of the multiple values in the J anchor.  

I am still looking for a way to join the L & R anchors on the 1 to Many relationship.  

Any thoughts?

[TimN]

Hi,

As an example, you want to match 25000 + 500 to 25500?  Do you know that the entries 25000 and  500 are related or is the amount the only data element you have to go on?  

Regards,

Tim

[binu_acs]

@J-Riedel One way of doing your second requirement. Can you elaborate on your first question?

[J-Riedel]

@TimN Yes, you are correct in the I need to match the 25,500 to the 25,000 + 500.  Unfortunately, the amounts are the only data elements I have.  There are no other fields that would match between the 2 data sets.

@binu_acs  - Elaborating on the first question (in my AYX workflow) where I am looking to join the L & R anchors.  The L anchor could join to multiple records in the R anchor.  See my example to Tim above, where the item in the L anchor is $25,000 and this will match up to the $25,000 + $500 from the R anchor.

Looking for a way to join all possible when I only have the amount field to work with.

Thanks!!

[TimN]

Hi,

Is it always 2 amounts that will be matched against 1 or could it be 3 to 1, etc.?

Tim

[J-Riedel]

Could be 1 to 3, 1 to 4, etc.  There is never a specific amount in the many