This example shows how the Execute
R action can be used to add row and column totals to a table, replacing
any null values in the table with zeros. Given an input table that looks
like this…
… the example produces a result table that
looks like this.
Note that the input table should have an initial
column which names each of the rows, however it may have an unknown number
of additional columns.
Using the Execute
SQL action, we might achieve this using three separate looping constructs.
The first loop would replace the nulls with zeros in each of the value
columns. The second loop would sum each of the columns, and the third
loop would sum each of the rows, generating dynamic SQL to reference each
of the columns.
Using the Execute
R action, we can achieve the same results using the following R statements.
The source table is provided in a Table parameter named Table1, and the
result table is stored in a Table dataset named Result.
The comments above each R statement explain
its purpose.
/* Create
a vector of column names, excluding the first column */
vnames
<- Table1_col_names_[-1]
/* Create
a data frame which stores each column in vnames as a numeric vector */