Alteryx auto assigning empty column Double data type

[Aastha]

Hi all,

I have an input file in Excel (attached 'Test file with Empty column.xlsx') with 2 columns, ID and Status. Status column is empty.

Excel input file:

ID	Status
1
2
3

Problem Statement: When I read this file in Alteryx, Status column is assigned the data type Double but I would like it to be of String data type.

Tried 3 methods to change the data type from Double to String:

• Formula tool - Using the Expression to replace Status column: ToString([Status], 0)
• Multi-Field Formula tool - Changing output type of Status column to V_String with the Expression: IIF(IsNull([_CurrentField_]),'',[_CurrentField_])
• Select tool - Changing the data type to WString

When reading the file after applying each transformation as specified above, the Status column is still assigned Double data type. 

2 workarounds/solutions to change the data type from Double to String:

• Change settings in Input tool and use Dynamic Rename tool
  • Step 1 - Connect the file to an Input tool and check 'First Row Contains Data'
  • Step 2 - Use Dynamic Rename tool with Rename Mode: 'Take Field Names from First Row of Data'
• Multi-Field Formula tool
  • Step 1 - Changing output type of Status column to V_String with the Expression: IIF(IsNull([_CurrentField_]),' ',[_CurrentField_]) and write to a file
  • Step 2 - Read the above Excel as input and use a Data Cleansing tool to remove leading and trailing whitespaces and overwrite the Excel file

Questions:

• Is there a better way to convert the data type of empty column from Double to String keeping other columns intact?
• How does Alteryx assign the data type for empty/null columns? (Note: I tested with other files and it does not depend on the data type of the first column.)

[DavidSkaife]

Hi @Aastha 

In regards to your first question, changing the field type to WString does work in the Select Tool, unless i'm missing something?

[flying008]

Hi,@Aastha 

******

Q1: Is there a better way to convert the data type of empty column from Double to String keeping other columns intact?
A1: Yes, there is a smart resolution of Dynamic Metadata for you, it can automatic convert empty columns type to V_String and keep other columns nature.

Tips: If your non-empty columns have null row, you must use Data-Cleansing tool to remove null rows first.

******

Q1: How does Alteryx assign the data type for empty/null columns? (Note: I tested with other files and it does not depend on the data type of the first column.)
A1: This is a very interesting topic, but personally I found that there is no pattern, the empty column type may be Double or Bool or other.

[Emmanuel_G]

Hi @Aastha ,

With select tool, you can edit the type easily ( Take a look at screenshoot below ).

However, keep in mind that some conversions will not work, such as converting strings to numeric type (Int, double or fixed decimal).

Doing such a conversion will allow you to change type but your column values ​​will become null so you will lose data.

[Aastha]

@Emmanuel_G , @flying008 and @DavidSkaife Thanks for your response. I originally had output a file from Alteryx with a blank column of datatype “String”. But when I read the same file back into Alteryx, that column was read in as “Double”. 

This creates a problem because when I am building an analytic app and the interface tool is not picking up the column that I want with “String” datatype due to it being “Double”.

So the 2 solutions I mentioned in my post did not work and should not be considered as a solution. I am still looking for a way to solve the problem above.