Changing the data type of a column
After you create a dataset, you can create tables from the fields in the dataset. Each column in the table created is assigned a data type.
Understanding data types
Each table column can be one of the following data types:
The icon in the column header indicates the data type of the column. The following screenshot shows the icons for the different data types.
Changing a column's data type to or from Array, Object, or Unknown is not allowed.
If a column is a derived column, its data type depends on the output of the last Map function executed on the column. For information about the output type of a Map function, see Map functions.
About the Mixed and Unknown data types
The Mixed and Unknown data types are less common than the other data types. The following list describes when you see these data types:
- A column's data type is Mixed when there are different types of data in the column.
A column's data type is Unknown after you use the Add a column option in the column drop-down menu. Manually changing the data type for this column is unnecessary because when you perform an operation to populate it with data, the operation automatically assigns the appropriate data type.NOTE: The terms Unknown and Undefined are used interchangeably in Xcalar Design.
Different ways to change the data type
You can use one of the following methods to change the data type:
- Use the Change data type option in the column drop-down menu.
- Use the Smart Type Casting feature. This method changes the data type for one or multiple columns.
These methods convert the selected column to a different data type. They do not create additional columns. If you want to create an additional column based on the selected column, you must use the Map function as described in Using the Map function to create new values.
Using the Change data type option
Follow these steps to change the data type for one column:
- Right click the column header to display the column drop-down menu.
- Select the Change data type option in the column drop-down menu.
- Select the data type in the menu.
Using the Smart Type Casting feature
Xcalar Design provides data type suggestions for columns in your table. Follow these steps to change the data type for one or multiple columns:
- Right click the table title to display the drop-down menu.
- Select Smart Type Casting…. The SMART CAST panel is displayed.
- The SMART CAST panel shows the data types suggested for particular columns. To change a suggestion, click a data type and select a new data type from the drop-down list.
(Optional)For any column that is not in the SMART CAST panel, you can click anywhere in the column to place it in the panel. Then click the data type for the column and select a new data type from the drop-down list.
- (Optional) If for any reason you want to remove all columns and their data types in the panel, click CLEAR ALL.
- Click CAST.
How strings with non-numeric characters are cast to Integer or Float
When all characters in a string are valid characters in a numeric value, the characters remain unchanged after you cast the string to Integer or Float. However, if a character in the string is invalid for a numeric value, the resultant value might be different from the original value. The following table shows the results of a data type change for some string examples:
|String value before casting||Explanation for the string value||Integer value after casting||Float value after casting|
|Empty string with no characters||0||0|
|abc||No numbers in the entire string||0||0|
|5||The number "5" preceded by one or more spaces||5||5|
|5ab||The number "5" followed by non-numeric characters||5||5|
|5||The number "5" followed by one or more spaces||5||5|
|5 6 7||Numbers separated by spaces||5||5|
|a5||A non-numeric character followed by the number "5"||FNF||FNF|
|a.5||A non-numeric character followed by a period and the number "5"||FNF||FNF|
|.6b||A period followed by the number "6" and a non-numeric character||FNF||0.6|
|0.9||The number "0" followed by a period and the number "9"||0||0.9|
|.5||A space followed by a period and the number "5"||FNF||0.5|
|.5||A period followed by the number "5"||FNF||0.5|