Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Filtering out empty tables

Highlighted
Meteoroid

Hi,

 

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?

Highlighted
Alteryx Certified Partner

Hi @Incis,

 

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:

A B

1 3

2 4

 

TABLE 2:

B C

5 7

6 8

 

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.

 

Luke

Keyrus UK

Highlighted
Bolide

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.

 

Union Tool Config.PNG

Highlighted
Meteoroid

Hi @Iminors,

 

Thank you for your reply.

Two distinct tables such as: 

 

Table 1.PNGTable 2.PNGSince they are totally different, I would not like them to union. Ich need a "if" condition which does the following: if table 2 is empty, not union; if table 2 is not empty, union.

Highlighted
Bolide

Hi @Incis,

 

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!

Highlighted
Meteoroid

Hi @DultonM,

 

 

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:Table 3.PNGTable 4.PNG

 

Table 5.PNGI 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.Table 1.PNGTable 2.PNG

 

 

Bolide

Hi @Incis,

 

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.

Highlighted
Meteoroid

Hey Dulton,

 

I have been suspected this "#1" symbol. Good that you confirmed!

 

Thanks!!

Labels