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.

[CoG]

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 @CoG,

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 @CoG , 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.

[Meoth]

Hi all,

Apologies for the late response. Thanks for all the prompt replies. I found the issue in the flow. There are few missing records in Duplicate anchor of unique tool which results in less Act and Cons value. Similarly, the right output join anchor has few records which results in less Act and Cons value.

Thanks again for the response!

[Qiu]

@Meoth 
Glad to know it gets solved afterall.