When data is first imported into Alteryx from a CSV, all fields and their data are imported with the data type “V_String”. This includes fields of data in your dataset that may actually contain only numeric values. Having numeric values imported as strings causes problems further down the road in your workflow when you want to perform any kind of mathematical operation, such as with the Summarize Tool.
Complications Related to Import as V_String
Because the fields in your dataset have the datatype “V_String” after importing from a CSV file, the Summarize Tool will not allow you to use the “Sum” function on the field, and other functions like “Max” and “Min”, which are typically used on numeric fields, will not yield expected results:
Depending on the style and formatting of the numeric fields, the values on import may also be imported with leading and/or trailing spaces. If you intend to use the data imported as a numeric field, this presents a further complication:
To check whether numeric values in your dataset are being imported with leading and/or trailing spaces, click on the outgoing connector of the Input Data tool after running a workflow.
Converting from V_String to Numeric Data Type
In order to get the data imported back to a proper numeric data type in Alteryx after importing from a CSV, we must use the Select Tool to reset the data type of the numeric fields to a numeric data type. Before we convert the fields to a numeric data type, however, we must “cleanse” the values while still in their V_String format of any special formatting or problems in the data that will prevent Alteryx from properly converting it. Such special formatting or problems could include the following:
- Commas delineating units of ‘000s in a number (e.g. “1,000.00” instead of “1000.00”)
- Parenthetical Negatives (e.g. “(1000.00)” instead of “-1000.00”)
- Trailing Negatives (e.g. “1000.00-” instead of “-1000.00”)
- Leading or Trailing Spaces
Only after each of the above “impurities” are removed can all the field’s values be properly and accurately converted to a numeric data type by Alteryx. Fortunately, each of these impurities can be consistently dealt with using various tools in Alteryx.