Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Upon success, workflows should run without warnings, conversion errors, and errors!! When your workflow runs as intended and passes all the validations, that “workflow finished” line should be BLACK, not yellow, orange, or red.
Did you know you are losing data or creating inaccurate calculations with every warning and field conversion error? These messages often mean real values are replaced with nulls.
Having a workflow full of warnings is the most common maintenance distraction I see in everyday workflows. When the results log is cluttered, how can you trust your output quality or know whether your workflow ran successfully?
I recommend preventing warnings and field conversion errors rather than ignoring or hiding them. The rest of this article shows my favorite prevention tricks for some common warnings.
Index
or, a Workflow Warning Species Taxonomy
- Index
- Family: Data Combination
- Common name: Field Not Present
- Numeric Group By
- Missing Field
- Duplicate Field Names
- More Than 16 Records
- Data Type
- Truncation
- Date Format Problem
- Wide String Conversion
- Value + Data Type Mismatch: Existing Value
- Calculation
- Value + Data Type Mismatch: Calculated Value
- Value + Data Type Mismatch: Calculated Value (Sneaky Version)
- Divide by Zero
- Date Parse Problem
- Lost Information in Conversion
- Data Inputs
- Code Page Problem
- CSV Quote Problem
- Conclusion
Look out for those warnings!
Family: Data Combination
Common name: Field Not Present
Latin name: agrum non praesens
Genus: Warning
Habitat: Union
Example text: Union (55) The field "Field7" is not present in all inputs.
Prevention: Overall, I recommend immediately removing any unnecessary fields upon data input. In practical terms, add a Select tool after your Input Data and deselect all fields that are not immediately needed. Also, be sure to deselect “Dynamic and Unknown.”
This will help organize your data, make your workflows run faster, assist with audits and data privacy, make it easier to validate that all the required fields are present, and, lastly, prevent many of these Union warnings. After you have removed any unnecessary fields, you can assess the remaining Union warnings.
Assessing the remaining Union warnings:
If your Union tool’s inputs are only 1-6 fields different, remove those fields beforehand OR add (“initialize”) those fields as null (or 0 or other value) before the Union. If those fields ever change, you’ll get a meaningful warning from your Union. It does not change the outgoing dataset since the Union would populate the missing fields with nulls anyway.
You can initialize those fields using the Ensure Fields CReW Macro, adding them to a Text Input tool added to the Union, adding them as null to a Formula tool (shown below), or through a variety of other dynamic design patterns.
If you expect fields to be significantly and regularly varied in your Union inputs, it is sometimes acceptable to mark the Union configuration to “Ignore” when fields differ. I recommend only using this as a last resort if you truly don’t care about uncontrolled null values in your columns.
I never recommend using “Manual Config” or “Auto Config by Position” in production workflows.
Numeric Group By
Latin name: coetus numero
Genus: Warning
Habitat: Summarize, Join
Example text: Summarize (65) Group Bys on Double or Float are not recommended due to rounding error.
Join (83) Joins on Double or Float are not recommended due to rounding error.
Prevention: When you have numeric fields as join keys or group bys, use a Select tool to change the data type to Int64 (for integers) or Fixed Decimal (for decimals) before grouping or joining. The “decimal place” on the FixedDecimal Size is actually the “scale,” which indicates the number of decimal places to be included in the data. This scale is fully customizable, so choose based on your data. More info here.
Missing Field
Latin name: absentis columna
Genus: Warning
Habitat: tools with Select interfaces (Select, Join, Append Fields, etc.) and tools with “group by” functionality (Transpose, Crosstab, Sample, Multi-Field Formula, etc.)
Example text: Select (91) The field "Field5" is missing. Compare the tool configuration with the input stream.
Prevention: as a best practice, when you read in data, immediately limit your incoming fields to those strictly needed. In practical terms, add a Select tool after your Input Data and deselect all fields that are not immediately needed. Also, be sure to deselect “Dynamic and Unknown.”
When this is not possible, you can also use a Dynamic Select tool to conditionally remove or include fields. For example, removing every field that starts with “Right_” or “Field_”. Or consider removing all blank fields in the Data Cleansing tool.
Clearing the warning once it has occurred:
- Select or tools with Select interfaces: Options > Forget Missing Fields
- “Group by” functionality: click through each tool with a warning. Clicking on the tool will reset the configuration for the current metadata. Be careful to make sure the column metadata is flowing, otherwise you will accidentally erase the configuration in your tool.
Duplicate Field Names
Latin name: duplicata nomina
Genus: Warning
Habitat: Select, Join, Input Data, Text to Columns, DateTime, other tools that can change field names
Example text: Select (73) There were multiple fields named "Field6". The duplicate was renamed.
Prevention: De-select or rename the conflicting field(s) as early as possible, even in the source data as needed.
In the below screenshot, renaming [Field6 v2] to “Field6” caused a conflict with the original field [Field6]. To fix this, deselect the original [Field6], rename the original [Field6], or choose a different rename than “Field6” for [Field6 v2].
More Than 16 Records
Latin name: tot ordines
Genus: Warning
Habitat: Append Fields, Join Multiple
Example Text: Append Fields (105) There were more than 16 records in the source.
Prevention: Ensure your input with the largest number of rows is connected to the Target anchor. When I use Append Fields, I typically only “multiply” by 1-3 new rows. Since this is fewer than 16 records, it will not raise this error when attached to the Source anchor.
Alternatively, if you are absolutely sure you want to cross-multiply your data, configure your Append Fields tool to “Allow All Appends”.
Although this same option exists for Join Multiple, I would recommend being VERY CAREFUL when using multi-dimensional joins. This is an easy way to crash your computer, database, or Alteryx Server. Honestly, I prefer to assign a simple sequential integer field (like a Record ID) as an additional join key. This allows me to perform a more controlled cross-join.
In sum, only allow all multidimensional joins if you know what you are doing, have proper limitations in place, and are willing to handle the consequences of it going wrong!!
Data Type
Truncation
Latin name: truncatum
Genus: Field Conversion Error
Habitat: Input Data, Select, other fields that can change field sizes
Example text: Input Data (1) The field "Field1" was truncated in record #3
Select (119) Field1: "3.1415926535 8979323846 2643383279 50288..." was truncated to 200 characters
Prevention: Increase the field size in the Select or Input Data tool.
Text: Update Field Size in Input Data option 7
Text: Update field Size in Select
Date Format Problem
Latin name: non validum diem
Genus: Field Conversion Error
Habitat: Select, other tools that can change field types
Example text: Select (38) Field8: 9/5/2024 is not a valid Date
Prevention: Use the DateTime tool or DateTimeParse() in a Formula to change the format of the date string into a proper ISO-8601 (YYYY-MM-DD) formatted date before changing the field type to Date. You could combine this into a single step in the Multi-Field Formula tool. I keep the DateTime specifiers documentation page bookmarked because I prefer using DateTimeParse() instead of the DateTime tool.
Wide String Conversion
Latin name: mores conversionem
Genus: Field Conversion Error
Habitat: Input Data, Dynamic Input, Select, other tools that can change field types
Example text: Input Data (7): Field_1: "Field1" could not be fully converted from a WString to a String.
Prevention: Ensure the field stays as a W-type string, either V_WString or WString. The W stands for “Wide,” meaning it can fit Unicode characters. More information here.
The “Forced” option means the field is already a V_WString. “V_WString: Forced” will maintain the V_WString configuration even when the incoming data auto-configures to a different data type. If this is a Select tool you click on often, be careful when the data types change. Clicking on a tool after a metadata refresh can undo certain configurations such as the forced data type.
Value + Data Type Mismatch: Existing Value
Latin name: valorem non congruit
Genus: Field Conversion Error
Habitat: Select, Multi-Field Formula, other tools that change field types
Example text: Select (40) Field3: N/A is not a number.
Multi-Field Formula (100) Field4: $44 is not a valid number.
Prevention: Add a Formula beforehand to correct the offending value(s), then change the data type. You can use Replace(), an IF statement, regex, or various other functions. Each method comes with pros and cons.
Calculation
Value + Data Type Mismatch: Calculated Value
Latin name: calculus non congruit
Genus: Warning, Error
Habitat: Formula, Multi-Row Formula, Multi-Field Formula
Example text: Multi-Field Formula (97) The formula "Field4" resulted in a string but the field is numeric. Use ToNumber(...) if this is correct.
Prevention: wrap a conversion function like ToNumber() or ToString() around your existing expression.
Or, if you’re using Multi-Field Formula, change your output data type to a compatible type. In this example, use a string type like V_String instead of a numeric type like Double.
Value + Data Type Mismatch: Calculated Value (Sneaky Version)
Latin name: versio subdola
Genus: Warning
Habitat: Multi-Field Formula, Multi-Row Formula
Example text: Multi-Field Formula (11) The formula "Field4" resulted in a integer but the field is numeric. [sic]
Prevention: Theoretically, an integer IS numeric. That’s why I consider this warning sneaky. There is clearly some underlying difference for Designer when considering a decimal vs integer value.
To clear this warning, convert the integer value to a decimal. I usually accomplish this by multiplying the final value by 1.00 or using Round([Column], 0.01).
Divide by Zero
Latin name: divide a nulla
Genus: Warning*
Habitat: Any tool with an expression editor, such as Formula, Multi-Field Formula, Multi-Row Formula, Filter
*In Filter, it can be an error, not a warning.
Example text: Formula (23) Divide By 0 Record #1 in field Result
Prevention: Wrap the offending expression in an IF statement to only perform the division when the denominator != 0. Conversion error prevented.
Date Parse Problem
Latin name: errat identitatis
Classification: Field Conversion Error
Habitat: DateTimeParse() functions
Example text: Formula (25) DATETIMEPARSE: Cannot convert "N/A" to a date/time with format "%m/%d/%Y" and language "English": Expected a number for Month: 'N/A'
Formula (25) DATETIMEPARSE: Cannot convert "2024-09-08" to a date/time with format "%m/%d/%Y" and language "English": Month number is out of range 1..12: '2024-09-08'
Formula (25) DATETIMEPARSE: Cannot convert "2024-09-08" to a date/time with format "%m/%d/%Y" and language "English": Year number is out of range 1400..9999: '2024-09-08'
Prevention: In the example text above, the provided DateTime specifiers do not match the actual format of the date string. The specifiers %m/%d/%Y are expecting a date like this: 10/12/2024. This is why the error message provides the entire string again at the end of the message. In simple cases, all you have to do is make sure your specifiers match your incoming strings.
Prevention is still possible in more complex cases where you have mixed patterns. In the best-case scenario, create a data validation process on your source data or data entry system so the data is standardized when it arrives.
When source-level prevention is not possible, you can wrap the DateTimeParse() in an IF statement with regex to evaluate the pattern before applying the parse. Here’s an example pattern that works for mm/dd/yyyy or dd/mm/yyyy: \d{1,2}\/\d{1,2}\/\d{4}
This regex pattern matches 1 or 2 digits (numbers 0-9), followed by a forward slash (/), followed by 1 or 2 more digits, another slash (/), followed by 4 digits. If you’d like further explanation of regex patterns, AI/LLMs are great at explaining regex patterns in plain language.
Lost Information in Conversion
Latin name: pereunt translatione
Genus: Field Conversion Error
Habitat: Formula, Multi-Row Formula, Multi-Field Formula
Example text: Multi-Field Formula (101) TONUMBER: $5550.00 lost information in conversion
Multi-Field Formula (97) Field5: 5,550 stopped converting at a comma. It might be invalid.
Prevention: An incompatible value causes this conversion error. To prevent it, correct the offending value(s) before changing the data type. You can use Replace(), an IF statement, regex, or various other functions. Each method comes with pros and cons.
If you change the data type in the middle of an expression, your correction (like ReplaceChar) must be nested inside the conversion function (like ToNumber). See the example below.
Data Inputs
Code Page Problem
Latin name: ineptias ostendit
Classification: Subversive field conversion problem that does not display an error.
Habitat: Input Data, Dynamic Input
Example: mystery nonsense characters displayed instead of accented or non-English characters.
Prevention: Change the code page in your Input Data to a code page that adequately supports large characters, such as UTF-8 or UTF-16. Even better, update your default code page in User Settings. Which code page is large enough will depend on your language and system. If the right code page is not available in the Input Data tool, you can use the ConvertfromCodePage() function to convert after reading the data.
CSV Quote Problem
Latin name: apostrophe fallor
Classification: Warning, Error
Habitat: Input Data, Dynamic Input
Example text: Input Data (113) Record #4: An unescaped quote was found: ""
Input Data (113) CSVFile: A field was missing a closing quote in record 4 (end of file): ""'' quotes'
Prevention: These are tricky because they are highly dependent on how your csv file was generated. Sometimes you get a simple fix by changing the type of escape character around extra delimiters. In the provided example, you can completely clear the warnings by changing Option 9 from “Quotes” to “Single Quotes.” Experiment with this option on your csv and see if any of these configurations works for you.
Other times, the only way to clear the warning is to fix the previous process generating the csv. If the previous process is an Alteryx process, make sure the “Quote Output Fields” configuration is set to “Auto” or “Always.”
If the previous process is not Alteryx but you have access to the configuration or code, see if there is a similar option for quoting output fields or escaping delimiters.
If you get the “missing closing quote” message as an error instead of a warning, you might need to adjust your delimiter in Input Data Option 5. If you just need it to read and don’t care about lost data, you can force the error to be a warning by checking the box for Option 10.
If all else fails, my last resort is generally to read the csv data undelimited and split it to columns or parse it myself. Change Option 5 to “\0,” which tells the input to use no delimiter, and deselect the box for Option 6, which controls whether the column names should output as headers (checked) or values in the first row (unchecked). The input should read in all as a single column, and then you can use other tools to split it back out to columns.
Conclusion
Congratulations on completing the field guide on warnings and conversion errors! I hope this empowers you to prevent the most common warnings and conversion errors you will encounter in the wild. Remember, preventing these warnings will clear up your results log so that it will be immediately obvious if a new problem occurs in your data processing.
If building better workflows excites you, I recommend learning about workflow optimization techniques (generalizability, scalability, maintainability) and diving into the world of custom validations and data processing alerts like workflow events. These powerful design patterns let you control when and how your workflows fail, and how your workflow notifies your user(s).
Cheers to an uncluttered results log and better workflows! 🥂
