Using the Map function to create new values
The Map function is divided into categories of functions that create new values to help you derive meaning from your dataset. The new values can be based on one or multiple selected columns. For example, you can perform an arithmetic calculation from values in two selected columns to create a third column that contains the results of the calculation. The new value may also be generated without any input. For example, you can generate a column of unique, random values.
This section describes the steps for performing a Map function. For descriptions of all Map functions, see Map functions.
How a Map function works on Mixed, Array, or Object columns
If you use a Mixed column as input to a Map function, the function is run on the cells of a data type appropriate for the function. For all other cells, the function returns FNF. For example, if a Mixed column is the input for the div function, the division is performed only on numerical cells. FNF is displayed for each non-numerical cell in the resultant column.
If you use an Array or Object column as input to a Map function, the input value is treated as FNF.
For more information about FNF, see Working with a table containing FNF.
Creating a column
Follow these steps to create a column with the Map operation:
Right click the header of the column from which you want to create new values. In the drop-down menu, select Map…. The MAP panel is displayed.The following list describes the rules and tips for entering information in a Xcalar Design panel.
- By default, the name of the column from which you start the function is displayed as an argument. If you want to use another column as an argument, you can change the argument to the name of another column.
- When typing a column name, always start with the dollar sign ($).
- When typing a column name, always include the prefix if one exists. (A derived column does not have a column name prefix.)
- If the panel calls for the resultant column name, type the name without preceding it with the dollar sign ($).
- To avoid mistakes when typing a column name as an operand, place the insertion point in the argument field, move your cursor to the active table and click anywhere in the desired column. The column's name is automatically entered in the operand field.
- If an operand accepts a column name, it also accepts the aggregate name that you created when you calculated the aggregate value for a column.
- When typing a decimal number for an operand, do not start with the decimal point. For example, type 0.5 instead of .5.
- If necessary, click in the panel to bring the specified table into focus. You can then scroll the table to make the desired column visible.
- When typing a string, do not enclose the string with double quote marks (").
When typing a string as input, escape the following characters with a backslash (\) if you want the characters to be interpreted literally:
- backslash (\)
- double quote mark (")
- dollar sign ($) if it is the first character
- caret (^) if it is the first character
- You can undo any typing with the undo key or key combination that you normally use in your browser.
Boolean values are case-insensitive. For example, in an input field that accepts a Boolean value, you can type true, True, false, or False.
In the MAP panel, follow these steps to select the function:
- Select the category. The functions in the selected category are displayed.
Select the function.TIP: You can quickly locate a function by typing its name or part of a its name in the Search map functions... field. Functions with a matching name are displayed in the Category and Function columns.
Accept the default name for the new column or enter a new name.
- Based on the description of the function, fill out the operand fields.
- Accept the default name for the new column or enter a new name.
- (Optional) Select Include only erroneous rows if you want to filter the resultant table such that only erroneous rows are included. For more information about erroneous rows, see Creating a table with only erroneous rows.
- Click MAP.
Example of using the type-casting function
After you select the Map… option in the column drop-down menu, the MAP panel is displayed.
Follow these steps to create a column with the desired data type:
- In the Table field, if the desired table name is not already displayed, click the down arrow to display a list of tables and then select the desired table.
- In the Category field, select type-casting.
- In the Function field, select the desired data type.
The first argument (x) is the active column name by default. You can accept the default or enter the name for another column whose data type will be changed.
If the desired data type is integer, enter the base as the second argument. For example, for base 10, type 10; for binary, type 2.
- Type a column name in the New Resultant Column Name field.
- If you are interested only in the rows where the Map function fails, select Include only erroneous rows. For example, if you want to know if there are values in a particular column that cannot be type cast into Integer, select this option. The resultant table will contain only rows with data in this column that cannot be changed by the Map function.
Click Map. A new column is created with the new data type.NOTE: If you did not enter the second argument, an error message is displayed, requiring you to click the No Argument check box. After you click the check box, click Map.
The following sample Map panel illustrates how you can change the data type to integer.
After you add a blank column to your table, the column contains no values and the column's data type is Unknown. You can initiate a Map function from such a column. However, not all functions displayed in the MAP panel accepts an operand whose type is Unknown. The following partial screenshot shows an example of a newly added column containing no values.
Result of Map initiated from an unpopulated column
Typically a Map function creates a new column containing values based on the input column. But if you run a Map function from an unpopulated column, the resultant column replaces the unpopulated column.
For example, from the unpopulated column, you can start the MAP panel to create a column of a particular data type, as illustrated in the following screenshot:
After the function is finished, the table contains a new column, as illustrated in the following screenshot: