This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We try to use Union tool to union datasets with different number of columns. Right now the Union Tool takes the columns of the dataset with more columns. It would be great if we could build a "if condition" say that if one of the dataset is empty, Union tool should take the columns of the non-empty dataset. Is there some way to do that?
The union tool concatenates data tables. This is done by aligning all columns with the same name and then keeping all the other columns included in each dataset that is being added. If there are columns from table 2 that aren't present in table 1 then the rows from table 1 will just have null values for each row of that column. For example:
TABLE 1 UNION TABLE 2:
A B C
1 3 -
2 4 -
- 5 7
- 6 8
If you need to remove the columns that aren't required then use a Select tool after the union to drop unwanted columns. Hope this helps, if not then please feel free to attach an example so I can understand your problem more.
There is also the option in the Union tool to "output Common Subset of Fields" which means only the columns that exist in all inputs (i.e. the column name appears in all tables being input to the Union tool) will appear in the output.
I think I understand your request better. Unfortunately, an 'if empty' condition in the Union tool doesn't exist. My recommendation would be to dynamically deselect fields that are empty after the Union is complete by using a combination of 4 tools: Record ID, Transpose, Filter then Crosstab. The technique is kind of hard to explain so check out the attached workflow. Hope this helps!
That is a great answer! As i said, if it is empty, they should not union; if it is not empty, I would like them to union. As the image shows, the column name changes from "city" to "Place". If i use "autofig by name" in Union Tool, the output of your tool would be:
I would assume you would suggest me using "autofig by position". Now I try again to union one non-empty table with an empty table by using "autofig by position": is it probable that the column names of the empty table will overwrite the column names of the non-empty table? The column names would be "Citizen", "Birthday" and "Gender in the output here.
You are right, the "Auto Config by Position" option would work well for your first example and for your new examples. The "Auto Config by Position" option chooses the field names of whatever connected input is the first (you'll see a "#1" on the connection line). Therefore, simply connect the data that has the field names you want to the Union tool first.