Reconciling Three Reports

Question

Hi. I have three reports. One has name of the participants, their class number. The second file has the class numbers and their respective exams names. The third file has the name of the participants, their class numbers and the exams they have actually taken. My goal is to find those who have not taken the required exam related to their group.

I have joined the first file with second file based on participants name, so I have a reference file that has participants, their class numbers and their respective exams. I then joined the result of this with the third file to find out if the participants have missed any exam, or if they have taken them, they are the correct ones.

I get confused when I get to the second join as I have duplicate columns and I'm not sure if the result of my second join is actually accurate. This seems like a very simple workflow but I need help please! I appreciate it.

OTrieger · Answer

@FarinazK 
This is the trick that I'm using when this is happening.
I'm adding a RecrodID tool before the L Input, and putting a Summarize tool after the J, Grouping By RecordID and Count RecordID, then I put a Filter tool Count > 1
Now I have all the items that are duplicated. Then I check the Left/Right sides against this list and find all the duplicates in it, or at least find the reason for the duplications.

Yoshiro_Fujimori · Answer

Hi @FarinazK ,

If there are duplicates in Join, I guess some join key is missing, but not sure.

Could you show us some sample data of table 1, 2, 3?