While working on one of the Alteryx Weekly Challenges (173) I encountered an instance where I had two columns full of city names. Origin column represented the starting location of the race and the Destination column represented the end of the race. I wanted to have a single column with all the unique city names. It turned out that it was completely unnecessary for the solution to the challenge but I will definitely be using this technique in the future. Here is a quick guide on how I did it.
Step 1
In order to isolate the values from both columns I used two summarize tools and grouped the values by individual columns.
Summarize tool 1:
Here I selected Origin as the column to group by and pressed run. This produced an output with a single column containing the names of the cities.
Summarize tool 2:
Here I grouped by Destination column to achieve the same outcome.
Step 2
I then went back to both summarize tools and changed the names of the “Output Field Name” to “City” so that I would have a matching field in both tables.
Step 3
To get both tables into one column I used a union tool. I connected both of the summarize tools to it and kept the default settings. After using the union I was left with a single column that had all the values from both summarize tools. Unfortunately, some of those values were duplicates...
Step 4
Finally, to get rid of the duplicates I added a unique tool and selected the only column in my table.
The output produced two outputs one that had duplicate cities in it and a second one which contained the information about all the unique cities from both of these columns.
