I am running a workflow that is interfacing with another database to pull data into the workflow. In the dataset that's pulled down, there appears to be two different types of null values: the usual Null that is handled by Alteryx, and a second type that appears as "1.#QNAN". I suspect this second type comes from a divide-by-zero error or something similar in the source database.
I would have expected the 1.#QNAN values to be parsed as strings, but they are still recognized as a numeric type (the column reads in as a Double type). However, if I ever try to look at the data directly, Alteryx crashes. If I don't preview the data in the Results tab, it does not crash. If I click only in the right places, I can run a Summarize tool with the Count Null option and these values are recognized as Null values. However, I can't find a way to reliably filter or process these values without Designer crashing. Trying to cast from a Double type to a String type still caused a crash.
Has anyone dealt with this before? I cannot find a way to even examine the data in Alteryx. I do not have the power or access to change the data in the source table.
This can be achived using the Multi-Field formula tool. Select all numeric field and pass on the below formula:
IIF([_CurrentField_]=null(),0,[_CurrentField_])
Basically 1.#QNAN is null() but due to some bug, it is visible as 1.#QNAN
Instead of 0, null() can also be used to convert 1.#QNAN to null