Join Multiple from 5 sources

Question

I am trying to join 5 sheets from excel all with the common denominator of employee ID.  When I run the program with the "Only Output Records that Join from All inputs" box checked, I only get roughly 300 records. However, because I am trying to recreate an excel spreadsheet, I know their should be 1500 records (trying to automate the process and output into Tableau). If I uncheck the "Only Output Records" box, I get more than a billion records that state "X#" records were generated by an N-Dimensional Join. Please Help.

unknown · Accepted Answer

There is nothing inherently wrong with the join tool. First thing I would check is the employee IDs to make sure none of them having leading or trailing spaces, or different value types such as one has leading 0s. To test this, use a summarise tool for each of the 5 sheets on just the employee ID and create a source field that has the file/sheet name for each record. Then union all 5 datasets together and then use another summarise tool that groups by Employee ID and Count the filenames. Filter for where count =5 and that will tell you how many employee IDs are shared between all 5 sources.

The benefit of this is you can see where the count is not 5 but where you expect it to be 5 and can validate that manually. I presume the XLSX sheet potentially may not be an inner join on all 5 sheets but could be a left join on some/the main one. If that is the case you cannot use the join multiple as this will only work for an inner join in your case.

Julcas · Accepted Answer

Hey Ryan I have a couple questions regarding your method.

1. I used the data cleansing tool to remove all white spaces. Would that solve the leading and trailing spaces as well?

2. What do you mean create a source field  that has file name for each record? Does that mean the change the output field name of the summarize tool?

3.  My output from the browse tool showed me a table with 5 columns each with a count of 5 (included). How would I be able to see how many employee IDs are shared between the 5 sources?

Also, I managed to seem to solve it by using multiple join tools joining two data inputs separately by inner join and by a left join (of the data set I wanted to base everything off of)