Alteryx Designer Desktop Discussions

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

Alteryx auto assigning empty column Double data type

Aastha
7 - Meteor

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.)
4 REPLIES 4
DavidSkaife
13 - Pulsar

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?

 

DavidSkaife_0-1658344340755.png

 

 

 

flying008
13 - Pulsar

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.

 

 

录制_2022_07_21_15_07_29_209.gif

Emmanuel_G
13 - Pulsar

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.

Emmanuel_G_0-1658399242656.png

 

Aastha
7 - Meteor

@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.

Labels