Avoiding duplicates in a one to many join

[Jaslusser12]

Hi,

I have two sets of data that I am trying to combine using a unique id - Visit ID. In data set 1, I have charges that relate to subsequent payments and need to marry the charge amount and date to the payment information. The problem is that when I join the data, the charge amount duplicates multiple times. 

Here is some sample data of my issue and the desired output. How do I avoid duplicating the charge amount?

Thanks,

[jdunkerley79 · Accepted solution]

I suggest:

Basically add a record ID to each row in both data sets using a Multi Row formula grouped on Location and Date

You can then add this to the join to ensure each row is only included once.

Finally a union is needed to create the full outer join

Sample attached