Union tables with different fields

Question

Hi all,

Please bear with me as I am an Alteryx novice. I will do my best to explain what I am trying to do...

I have two excel spreadsheets (let's call one File A and the other File B)  . I am familiar with how to import each of them into alteryx. The two spreadsheets have some columns that are the same (columns like State, City, Business Unit #, etc.) but they also have some columns that are different. For example, the first dataset has fields like Revenue, Sales per square foot, etc. And the other spreadsheet has fields like Cost per capita, Construction costs projected, etc. You get the point.

Business Unit # is the best identifier for the two spreadsheets, because there is only one unique business unit # per row in each file. The key here is that File A has a lot of the same business unit #'s that File B has. So there is some overlap. That, however, is fine. Ultimately, File A is my anchor file...it's the one I really want to be working with because it has all the columns I need. File B will have a bunch of the same business unit #'s as File A, but it will also have some business unit #'s that are not present in File A.

So here is what I want to do... first, I want to bring over those rows from File B to File A where the business unit # does not exist in File A. In other words, if File B has a row with business unit # of 8675309, but File A does not have that business unit # anywhere, then I would like that row in File B to be brought over to File A (the row would just be added at the bottom). I thought I could easily accomplish this with a Union tool, but that's when I realized I would have issues since there are all these fields in File B that don't exist in File A, and vice versa. What can I do about this? The tricky part is that some of the fields are technically the same, but just named differently. For example, File B might have a field called "Total construction cost" in column H but File A would have technically the same field in column C that is called "Constructions costs - total". How do I get the one from File B to end up in the same column as it exists in File A?

ALSO, there are some fields in File B that I simply don't want to bring over into File A...how would I go about that? Hopefully I explained this well. If not, please let me know and I would be happy to attach the two raw files here.

JBLove · Accepted Answer

@sgazar -

My suggestion would be to use a Select Tool after the File B Input.  Within the Select tool you can deselect the fields you don't need and rename the fields you do need to have the same column name as in File A.

Then you can use the Union Tool.

Within the Union Tool you can use "Auto Config by Name", and make sure to check the "Set a Specific Output Order" at the bottom.  Use the default ordering, and it's important to make sure that File A is connected to the Union as #1, and File B is connect to the Union as #2.  The connection lines feeding the Union Tool should be numbered.  But so long as you connect FileA first then FileB the ordering will be correct.

Then follow this up with a Unique Tool.

You can remove the duplicate rows by simply selecting the Business Unit Number Column.  Since we forced the processing order in the Union Tool, the duplicate row will always come from FileB.  My assumption based on your initial post is you want to ignore the overlapping records from FileB.  However, if you do want to check for consistency in value between FileA and FileB for the overlapping records then an alternate approach will need to be taken.

terry10 · Accepted Answer

@sgazar

It sounds like you are describing a Full Outer Join. see attached workflow.

(1) rename any common fields in input B and deselect unwanted fields

(2) join on Business unit (and optionally on common fields)

(3) union R, J, L anchors

If my reply helped you, please mark as a solution.