Missing Records in Join Tool

Question

Hi All,

I am currently trying to separate records out based on one common field using the join tool, this is in the container labeled Unique Errors. I use the select tool before the join tool to convert the join field in each file to the same data type. My issue is that the total number of records from the three join files produced does not match the number of records being inputted. Any advice on this would be greatly appreciated.

Thank you!

Comparison.yxmd

eaparsek · Answer

This definitely helps thank you so much! I was forgetting this nuance with the joint records. Thank you so much for the explanation :)

DataNath · Answer

No problem @eaparsek, welcome! I'm hoping I don't actually need to see the data and that this will explain it...

Let's say you have 1,000 records going into the Left (L) input, and 1,000 records going into the Right (R) input.

If every single one of these records has a unique identifier that finds a match in the opposite input, then you'll get 1,000 records joining to 1,000 records. Therefore, they join and get placed side by side.

The result of this is that you'll see 1,000 records coming out of the J (Join) output anchor, whereas in total you had 2,000 records going in. This isn't a loss of this data, but it has been placed side-by-side as part of the join.

(In your case, 'losing' 800 records would mean that 800 records are finding a match).

Hoping this helps and explains the problem without having to see the data!

eaparsek · Answer

Hi DataNath, thanks for the advice, this is my first time posting! Unfortunately, I'm working with sensitive data so I can't upload it along with the flow. The field I'm joining on is basically a VString representing a unique identifier. I'm mostly trying to understand logically where records could go missing in the join process, I'm having trouble understanding where about 800 records could've gone (out of around 11,000). Thanks for your reply :)