Unique formula

[Kirstyp]

This is probably so simple but I can't seem to get my unique tool to work.

I have joined 2 data sets into one, and all of the rows have multiplied so I am trying to unique certain fields, as I don't want to loose data from either input source. 

I have a reference from last year with items and a reference from this year with items, I want to be able to see the references from both years for each client, but not duplications example attached;

I have attached a example of the joined data and the wanted end result, any help would be appreciated as instead of having 2000 rows I have approx. 5000 so I really don't want to have to do any manual unique trawls.

[MarqueeCrew]

@Kirstyp,

When you joined the data, the unique 2,000 rows from source A were joined to records in source B where B had multiple rows for the same Key.  A and B could be mixed here, but if one source of data had the expected 2,000 rows and you ended up with 5,000 records then you either needed to add another field as the JOIN key or you need to put the UNIQUE tool in front of the A/B source data to limit the KEYs to the 1st record.

You can setup the UNIQUE tool with all fields selected and you'll get rid of the duplicates, but that doesn't fix the real problem.

Cheers,

Mark

[Kirstyp]

Hi

Thanks for your reply.

The 2 input (A and B) are both unique, based on client and reference item before the join??

[Claje · Accepted solution]

Hi,

I'm not sure what your Join fields are (since I think you filtered them out).

Can you tell me how you know for ref 27798 that Private Retirement Pension ties to Superannuation/Occupational Pension?

It seems like you might just be missing a Join criteria

[Kirstyp]

Hi

Appologies for delay. Yes both joins are unique prior to joining.

I can align the names later, I am purely trying to identify what benefits were in receipt the previous year and what is in current year, but its duplicating the joins?