Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Filtering out empty tables

Incis
6 - 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?

7 REPLIES 7
lminors
9 - Comet

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

DultonM
11 - 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

Incis
6 - 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.

DultonM
11 - 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!

Incis
6 - 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

 

 

DultonM
11 - 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.

Incis
6 - Meteoroid

Hey Dulton,

 

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

 

Thanks!!

Labels