Join to Find Difference Instead Creates Thousands of Duplicate Records

Question

I am having a join issue.

1,054 records from Input A.  Input A = 2021

3,222 inputs from Input B.  Input B = 2018-2021.

Every record in A has a corresponding record in B.  But, B has a few records missing from A.

I want to find the missing records in B and join them to A.  To do this I have to ignore prior years.

Right now my join is outputting 1.7 million records.

I have attached a poor attempt to replicate my issue at a much smaller scale.

UPDATE/CLARIFICATION:

In current example, A only represents 1 year of data.  In the real world, it could represent multiple years of data.  In both cases I am trying to find the missing records in B and join them to A.

I am also trying to avoid manual filters.  Ideally, I want A to filter B or align B based on year in A without human interaction.

Help. Thanks!

to_community_join_filter_qstn.yxzp

Emil_Kos · Answer

Hi @hellyars,

Now I get it.

If you are having duplicates in the data set you need to use sample tool or find and replace instead of joining.

I am not sure what another approach to propose. Maybe you can summarize the data for the duplicated positions in the data set B in order to have one line per ID?

hellyars · Answer

@Emil_Kos   In the real data I already knew how join (similar to what you suggested) if the Inputs were 1 year each and I could throw filters in here and there.   Things got complicated when B became multiple years (A can to).   I have 1,054 records turning into 1.7 million.  And I am only joining on Year and ID.

Emil_Kos · Answer

Hi @hellyars,

can you add year to the join as an additional joining field?