Numbers different after join tool

[Meoth]

Hello all,

I have a flow where I join the two Files using Join tool. Input 1 has 728,904 records. Input 2 has 578,391 records. The Final output after join is mismatching with the Inputs result. Below are the images.

Input1 result:

Input2 result:

Final result:

Below is the flow.

Input1 Fields:

Input2 Fields:

Unique tool to Input1: Year, Month, Date, Country, ID, Code, Stat.

Summarize tool to Input 2: Group by: Year, Month, Date, Country, ID, Code, Stat

                                           Sum: Act, Cons

Join Configuration: Carried all the Input1 fields and Input2 (Act, Cons) fields

Formula connected to Inner Join output: 

Appreciate the help. Thanks!

[Qiu]

@Meoth 
What you mean by "not maching",
Here is how it works

The total number of records of L and J anchors equals the number of records of Data Input 1 (L anchor for input)

The total number of records of R and J anchors equals the number of records of Data Input 2 (R anchor input)

[Meoth]

@Qiu , Thank you for your response.

I meant the Act and Cons value is different between the Inputs and Output after the join tool. Please see images in the post.

[AndrewDMerrill]

Are the two input records that you shared the only records for Date = 2023-01-29 & Stat = "Lag3"?

Is what you shared/can you check the 'L' & 'R' Inputs and the 'J' output. I would check the Summarize & Unique Tools, as those have the highest risk, based on what you've shared, of changing your output. 

Alternatively, Add a Filter Tool to Input 1 and track just the records that have [Date] = "2023-01-29" && [Stat] = "Lag3", this way you can more easily identify the source of the issue. It is very unlikely to be the Join tool.

[Meoth]

Hi @AndrewDMerrill,

Yes, the two input records that I shared here the only records for Date = "2023-01-29" & Stat = "Lag3"

[Qiu]

@Meoth 
I am sorry for the misunderstanding.
Agree with @AndrewDMerrill , maybe we can isolate the target record only and check if the Summarize tool changed the number.

[apathetichell]

usual culprits  1) something you think is unique is not unique. therefore instead of 1-1 matches you have a 1-> many match. 2) you are unioning your data correctly - but looking at the wrong fields - this is because something is renamed as Right_{fieldname} after join - but not when unioning the left/right unjoined anchors. 3) you are not unioning the left/right unjoined anchors correctly. 4) some combo of the above.