Using the function bar
The search bar displayed above a worksheet also serves as a function bar. The function bar is in effect when a column is selected. Initially, the function bar displays the operation that created the column. You can replace the operation with another one by typing a function name and its associated parameters as described later in this section.
The function bar is denoted with the function symbol, f(x). The following screenshot shows the location of the function bar.
How to type and execute an operation in the function bar
When entering an operation name in the function bar, always precede the name with the equal sign (=). After you finish typing, press the Return or Enter key to execute the operation.
Operations that you can execute from the function bar are described in this section. You cannot execute other operations.
Rules for entering names in the function bar
The argument to an operation in the function bar can be a column name, a field name in the Data Browser, or an aggregate name. (To learn about the Data Browser, see Data Browser. For information about viewing a list of aggregates, see Displaying saved aggregate values.)
Remember the following rules when typing the name:
- Verify the name is typed in its entirety. When copying it from the table, be aware that part of the column header in your table might be obscured. For example, the column name DayOfWeek_integer might be visible as DayOfWeek in a narrow column. A mistyped column name might produce unpredictable results. For example, the resultant column might be filled with FNF (field not found).
Make sure the column name prefix is included if the column is not a derived column. For example, if the prefix is Airlines and the name is Destination, type the following:
- You can use an aggregate value that you saved earlier. You must precede the aggregate value name with a caret (^). For example, if the average value for the salary column is average_salary, enter ^average_salary if you want to use it as an argument in the function bar.
The arguments entered are combined to form an evaluation string for the function, which must not exceed 4,096 characters. An error message is displayed if you try to pass an evaluation string that exceeds the limit. To keep the evaluation string below the limit, you might need to execute multiple functions instead of one.
Use the Pull operation to populate the active column with values from the column named in the operation. The source column can be a column already existing in the table or a column listed under the DATA header. After the Pull operation, the data type of the active column is changed to match the type of the named column.
You can execute all Map operations and pass the function names and argument values in the function bar.
You can also invoke a UDF in a Map operation in the function bar as shown in the following example, which invokes a UDF for converting column1 to the UNIX timestamp:
Example of a Map operation with nested functions
You can nest functions in an operation by using brackets. The following steps illustrate how to perform a Map operation with nested functions and store the resultant values in a new column:
- Click Add a column in the column drop-down menu to create a blank column.
- Click the header of the new column. Type the column name and press Enter. This column becomes the active column and the function bar displays f(x) =, which means that you can start typing in it.
- For each column to be used as a function argument, verify that its data type is either Integer or Float. Change the data type if necessary.
Enter the following Map operation:
map(mult(mult(mult(div(sub(AskPrice_float, BidPrice_float), AskPrice_float),100), div(add(BidSize_float,AskSize_float),2)),100000))
This Map operation is equivalent to the following formula:
This operation has nested arithmetic functions such as Add, Sub (subtract), mult (multiply), and div (divide). When you type an opening bracket, Xcalar Design automatically adds the closing bracket. If there are mismatched brackets, an error message is displayed when Xcalar Design tries to execute the operation.
The resultant values of the Map operation are stored in the column created in Step 1.