MAP
Description
MAP(reference_value, searched_dataset, reference_col, result_col)
Input | reference_value: the value used to position a row in searched_dataset. Type: all data types that a data column allows.
searched_dataset: the name of the dataset to be searched. Type: string.
reference_col: the column in searched_dataset where the reference_value locates in. Type: string (column name) or int (column index, starts from 1).
result_col: the column in searched_dataset where the output locates in. Type: string (column name) or int (column index, starts from 1). |
Output | Search the reference_col of dataset, position the first row whose value in reference col equals to reference_value, and the output is the value in result_col of the row. If multiple rows are positioned, only the first result is used.
Type: all data types that a data column allows. |
Examples
Suppose dataset [ds1] contains employee information and one row in [ds1] looks like:
EMPID | EMPNAME | ... | Salary |
1001 | Janet | 3000.0 |
MAP(1001,"ds1",1,2) >> Janet
MAP(1001,"ds1","EMPID","SALARY") >> 3000.0
MAP(1001,"ds1",1,"SALARY") >> 3000.0
SQL
Description
SQL(data_connection, query_statement, result_col, result_row)
Input | data_connection: the name of a data connection which connects to a database. Type: string.
query_statement: the SQL statement that query data from tables in the database. Type: string.
result_col: the index of a column in the query result. The output locates in this column. Type: integer.
result_row [optional]: the index of a row in the query result. The output locates in this row. Type: integer. |
|---|---|
Output | Excecute the query and return the value in result_col of result_row. If result_row is not specified, all values in result_col are returned.
Type: if result_row is specified, type can be all data types that a table field allows; otherwise, type is array. |
Examples
Suppose there is a table named "EMPLOYEE" in a data connection named "FRDemo", and the table looks like:
EMPID | EMPNAME | ... | Salary |
1001 | Janet | ... | 3000.0 |
1002 | Margaret | ... | 3000.0 |
... | ... | ... | ... |
1025 | Fanny | ... | 3000.0 |
SQL("FRDemo", "SELECT * FROM EMPLOYEE",2,1) >> Janet
SQL("FRDemo", "SELECT * FROM EMPLOYEE",2) >> Janet,Margaret,Andrew,Michael,Robert,Steven,Anna,Jackie,Rechia,Tom,Selina,Eve,Bob,Annasu,Happy,Susan,Geoge,White,Sheep,Evelyn,Sky,Zero,Fanny
TABLEDATAFIELDS
Description
TABLEDATAFIELDS(searched_dataset)
Input | searched_dataset: the name of the dataset whose data columns will be searched. Type: string. |
|---|---|
Output | The names of all data columns that belong to searched_dataset.
Type: array. |
Examples
Suppose dataset [ds1] has data columns: [Region], [Salesperson], [Product_types], [Product] and [Sales_Volume].
TABLEDATAFIELDS("ds2") >> Region,Salesperson,Product_types,Product,Sales_Volume
VALUE
Description 1 (2 or 3 parameters)
VALUE(searched_dataset, result_col, result_row)
Input | searched_dataset: the name of the dataset to be searched. Type: string. result_col: the column where the result locates in. Type: interger (column index, starts from 1) or string (column name). result_row [optional]: the row where the result locates in. Type: interger (row index, starts from 1). |
Output | Search the dataset and return the value in result_col of result_row. If result_row is not specified, return all values in result_col.
Type: if result_row is specified, type can be all data types that a data column allows; otherwise, type is array. |
Examples
Suppose dataset [ds2] looks like:
day | weather | temperature |
Monday | sunny | 31.0 |
Tuesday | windy | 26.1 |
Wedensday | windy | 24.0 |
Thursday | rainy | 22.0 |
Friday | storm | 21.0 |
Saturday | cloudy | 23.0 |
Sunday | sunny | 29.0 |
VALUE("ds3",2,1) >> sunny
VALUE("ds3","weather",1) >> sunny
VALUE("ds3",2) >> sunny,windy,windy,rainy,storm,cloudy,sunny
VALUE("ds3","weather") >> sunny,windy,windy,rainy,storm,cloudy,sunny
Description (4 or 5 parameters)
MAP(searched_dataset, result_col, reference_col, reference_value, result_index)
Input | searched_dataset: the name of the dataset to be searched. Type: string.
result_col: the column in searched_dataset where the output locates in. Type: string (column name) or int (column index, starts from 1).
reference_col: the column in searched_dataset where the reference_value locates in. Type: string (column name) or int (column indes, starts from 1).
reference_value: the value used to position a row in searched_dataset. Type: all data types that a data column allows.
result_index [optional]: the index of the output value in the result array. |
|---|---|
Output | Search the reference_col of dataset, position the rows whose values in reference col equal to reference_value, and the output is an array containing values in result_col of the rows. If result_index is specified, only output one element with index result_index in the array.
Type: if result_index is specified, type can be all data types that a data column allows; otherwise, type is array. |
Examples
VALUE("ds3",3,2,"windy") >> 26.1,24.0
VALUE("ds3","temperature","weather","windy") >> 26.1,24.0
VALUE("ds3",3,2,"windy",1) >> 26.1
VALUE("ds3","temperature","weather","windy",1) >> 26.1
COL
Description
COL()
Input | None |
|---|---|
Output | The index of the column where current cell locates in. Starts from 1.
Type: integer. |
Examples
COL() in cell B11 >> 2
COL() in cell C11 >> 3
COL() in cell D11 >> 4
This function can be used to judge whether the index of the column is odd or even:
COL()%2 in cell B14 >> 0 (i.e. index of the colum is even)
COL()%2 in cell C14 >> 1 (i.e. index of the colum is odd)
COL()%2 in cell D14 >> 0
ROW
Description
ROW()
Input | None |
|---|---|
Output | The index of the row where current cell locates in. Starts from 1.
Type: integer. |
Examples
ROW() in cell B17 >> 17
ROW() in cell C20 >> 20
ROW() in cell D23 >> 23
This function can be used to judge whether the index of the row is odd or even:
ROW()%2 in cell C17 >> 1 (i.e. index of the colum is odd)
ROW()%2 in cell C20 >> 0 (i.e. index of the colum is even)
ROW()%2 in cell C23 >> 1
Previous：SQL Function
