U.S. passports can now carry an “X” gender designation for those who do not identify as male or female, and many organizations are implementing similar policies. The emergence of additional genders upends a fundamental assumption in most data schemes. In Alteryx, the existence of new genders in some data files – but not in others – can crash workflows that assume the extra genders are available.
Assume a workflow was originally built using triple-gender (M, F, and X) data:
When cross tabbed, there are two records with M, F, and X columns. The High School record has a null for the X count, so a Data Cleaning tool turns the null into a zero so that the Formula tool can successfully add the three genders to get a total.
When we use the exact same workflow with a data set that does not have any records for Gender X, it fails at the Total formula. There is no X field, and the Formula tool errors out.
One solution is to create a lookup table of all organization-approved gender letters as shown below:
The revised workflow Joins the actual data against the Universe of All Gender Letters. This step discovers which gender letters are missing from the actual data and passes them through the Right output anchor. After going through the Cross Tab, record 3 of the lookup table (“X” and “0”) becomes a single field named X with a single value, zero. At the Append, this single field is tacked on to the actual data’s fields. When the flow reaches the Total formula, it sees all the fields that it expects – the M and F with data, and the X with zeroes.
Keeping a table of gender letters enables you to quickly update the workflow if your organization requires additional gender definitions in the future. Even if the actual data doesn’t include a Z gender, for example, the workflow will produce a Z field populated with zeroes, showing that the workflow is ready whenever the data is.
A different workaround to the lookup table of all possible gender letters is to first Union all the data sets before doing any math. An X field will be created by the Union for data sets that lack them. However, delaying any math steps may not be possible due to logic demands. Also, if none of the data sets contain the new genders, then users will be left wondering if they were dropped somehow by the logic, vs. simply not being present in the data.
