RCOMMAND command
Concept Information
Passes an Analytics table to an external R script as a data frame and creates a new table in the Analytics project using output from the external R script.
Syntax
RCOMMAND FIELDS field <...n> RSCRIPT path_to_script TO table_name <IF test> <WHILE test> <FIRST range|NEXT range> <KEEPTITLE> <SEPARATOR character> <QUALIFIER character> <OPEN>
Parameters
| Name | Description |
|---|---|
| FIELDS field_name <...n> |
The fields from the source Analytics table, or the expressions, to include in the data frame that is sent to the R script. Depending on the edition of Analytics that you are using, you may encounter errors when sending data containing some special characters to R:
Note Mixed language data is also not supported, for example a table containing both Japanese and Chinese characters. |
| RSCRIPT path_to_script |
The full or relative path to the R script on the file system. Enclose path_to_script in quotation marks. |
| TO table_name |
The location to send the results of the command to:
The output table is created from the data frame or matrix that the R script returns. |
| IF test optional |
A condition that must be met to process the current record. The data frame passed to the R script contains only those records that meet the condition. |
| WHILE test optional |
A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached. Note If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached. |
| FIRST range | NEXT range optional |
The number of records to process:
Use range to specify the number of records to process. If you omit FIRST and NEXT, all records are processed by default. Caution There is a known issue in the current version with NEXT when running the RCOMMAND. Avoid using this option as the record reference may reset to the first record regardless of which record is selected. |
| KEEPTITLE optional |
Treat the first row of data as field names instead of data. If omitted, generic field names are used. This option is required if you want to retrieve data using column names in the R script. |
| SEPARATOR character optional |
The character to use as the separator between fields. You must specify the character as a quoted string. The default character is a comma. Note Avoid using any characters that appear in the input fields. If the SEPARATOR character appears in the input data, the results may be affected. |
| QUALIFIER character optional |
The character to use as the text qualifier to wrap and identify field values. You must specify the character as a quoted string. The default character is a double quotation mark. Note Avoid using any characters that appear in the input fields. If the QUALIFIER character appears in the input data, the results may be affected. |
|
OPEN optional |
Opens the table created by the command after the command executes. Only valid if the command creates an output table. |
Examples
Getting R up and running (Hello world)
You create a hello world script to test your connection between Analytics and R:
Analytics command
RCOMMAND FIELDS "Hello", ", world!" TO "r_result" RSCRIPT "C:\scripts\r_scripts\analysis.r"
R script (analysis.r)
srcTable<-acl.readData()
# create table to send back to ACL
output<-data.frame(
c(srcTable[1,1]),
c(srcTable[1,2])
)
# add column names and send table back to ACL
colnames(output) <- c("Greeting","Subject")
acl.output<-output
Accessing field data using row and column coordinates
You send a number of invoice fields to an R script for analysis outside Analytics:
Analytics command
RCOMMAND FIELDS Department_Code Invoice_Amount Invoice_Date Invoice_Number Vendor_Number TO "r_result" RSCRIPT "C:\scripts\r_scripts\analysis.r"
R script (analysis.r)
# Retrieves invoice number from second row of data frame in R script srcTable<-acl.readData() srcTable[2,4]
Accessing field data using column names
You send a number of invoice fields to an R script for analysis outside Analytics. You use the KEEPTITLE option so that columns can be referenced by name in R:
Analytics command
RCOMMAND FIELDS Department_Code Invoice_Amount Invoice_Number TO "r_result" RSCRIPT "C:\scripts\r_scripts\analysis.r" KEEPTITLE
R script (analysis.r)
# Retrieves invoice number from second row of data frame in R script srcTable<-acl.readData() srcTable["2","Invoice_Number"]
Sending invoice records that exceed 1000.00 value to R script
You send a number of invoice fields to an R script for analysis outside Analytics. You use IF to limit the records sent to R. Only those records with an invoice amount exceeding 1000.00 are sent:
Analytics command
RCOMMAND FIELDS Department_Code Invoice_Amount Invoice_Number TO "r_result" IF Invoice_Amount > 1000.00 RSCRIPT "C:\scripts\r_scripts\analysis.r" KEEPTITLE
R script (analysis.r)
# Retrieves invoice number from second row of data frame in R script srcTable<-acl.readData() srcTable["2","Invoice_Number"]
Sending invoice records and returns multiplied invoice amounts
You send a number of invoice fields to an R script for analysis outside Analytics. The R script performs a single action against every cell in the named column:
Analytics command
RCOMMAND FIELDS Department_Code Invoice_Amount Invoice_Number TO "r_result" RSCRIPT "C:\scripts\r_scripts\analysis.r" KEEPTITLE
R script (analysis.r)
# Returns slice of ACL table with value doubled srcTable<-acl.readData() acl.output<-srcTable["Invoice_Amount"] * 2
Remarks
For more information about how this command works, see Running R scripts.
Referencing Analytics data in the R script
The Analytics table is passed to the script as an R data frame. Data frames are tabular data objects that may contain columns of different modes, or types, of data.
To work with the data frame created by Analytics in an R script, invoke the acl.readData() function and store the returned data frame in a variable:
# stores the Analytics table in a data frame called myTable that can be referenced throughout the script myTable<-acl.readData()
To retrieve data from a cell in the data frame, you can use one of the following approaches:
-
Using row and column coordinates:
# Retrieves the value in the first row and second column of the data frame myTable[1,2]
Note
Coordinates are based on the order of fields specified in the command, not the table layout or view that is currently open.
-
Using row and column names:
# Retrieves the value in the first row and "myColumnTitle" column of the data frame myTable["1","myColumnTitle"]
You must specify the KEEPTITLE option of the command to use column names.
Rows are named "1", "2", "3", and increment accordingly. You may also use a combination of names and coordinates.
Passing data back to Analytics
To return a data frame or matrix back to Analytics and create a new table, use the following syntax:
# Passes myNewTable data frame back to Analytics to create a new table acl.output<-myNewTable
Note
You must return a data frame or a matrix to Analytics when the R script terminates. Ensure the columns in the data frame or matrix contain only atomic values and not lists, matrices, arrays, or non-atomic objects. If the values cannot be translated into Analytics data types, the command fails.
Data type mappings
Analytics data types are translated into R data types using a translation process between the Analytics project and the R script:
| Analytics data type | R data type(s) |
|---|---|
| Logical | Logical |
| Numeric | Numeric |
| Character | Character |
| Datetime | Date, POSIXct, POSIXlt |
Performance and file size limits
The time it takes to run your R script and process the data that is returned increases for input data exceeding 1 GB. R does not support input files of 2 GB or higher.
The number of records sent to R also affects performance. For two tables with the same file size but a differing record count, processing the table with fewer records is faster.
Handling multi-byte character data
If you are sending data to R in a multi-byte character set, such as Chinese, you must set the system locale appropriately in your R script. To successfully send a table of multi-byte data to R, the first line of the R script must contain the following function:
# Example that sets locale to Chinese
Sys.setlocale("LC_ALL","Chinese")
For more information about Sys.setlocale( ), see the R documentation.
R log file
Analytics logs R language messages to an aclrlang.log file in the project folder. Use this log file for debugging R errors.
Tip
The log file is available in the Results folder of Analytics Exchange analytic jobs.
Running R scripts on AX Server
If you are writing an analysis app to run on AX Server and you want to work with external R scripts:
- Upload the file as a related file with the analysis app.
- Use the FILE analytic tag to identify the file(s).
- Reference the file(s) using the relative path ./filename.r.
Note
Using a related file ensures that the TomEE application server account has sufficient permissions to access the file when running R with Analytics Exchange.
