Data Transforms

What are they?

Data transforms allow you to manipulate the data in datafunnels.

Where can they be used?

Data transforms are available inside of datafunnel tags. Using them directly inside the datafunnel tag will change the datafunnel. Everywhere that references the datafunnel will get the new transformed data.

Data transforms can also be used in data-source tags. Using your transforms in data-source tags does not change the datafunnel. It only transforms the data where you are currently using it.

What are the different transforms?

The transforms include the following:

add-column
add-row
change-values
collapse-rows
column-reorder
external-transform plugin
filter
reverse-rows
sort

add-column, add-row

<ct:add-row exclude="rows:1,3,4;columns:1" output="###,###" function="rsum" add-label="c1:run title"/>

<ct:add-row exclude="rows:1,3,4;columns:1" output="###,###" function="ravg" add-label="c1:run title"/>

<ct:add-column exclude="rows:1;columns:1" function="sum" add-label="r1:sum title"/>

<ct:add-column exclude="rows:1-3" output="###,###" function="avg" add-label="r3:avg title"/>

<ct:add-row exclude="columns:1" function="min" add-label="c1:min title"/>

<ct:add-row exclude="columns:1" function="max" add-label="c1:max title"/>

<ct:add-row exclude="columns:1" function="mid" />

<ct:add-column exclude="rows:1" output="###,###" function="=(c1+c2)/lc3" add-label="r1:function"/>

Notes on add-row and add-column

Allows you to add a row or column to the datafunnel that is some mathematical computation of other rows and columns.

exclude takes any of the rows/columns syntax defined in filters.

output specifies the number format in Java NumberFormat String form for the row or column to be added.

Available Functions

sum
Sum each column when adding a row. Sum each row when adding a column.
avg
Avg each column when adding a row. Avg each row when adding a column.
group
(#, "0-100=A, 101-200=B") - Put A or B in the cell if the original number is the range.
min
Min of each column when adding a row. Min of each row when adding a column.
max
Max of each column when adding a row. Max of each row when adding a column.
mid
(the avg of min and max)
rsum
Running sum of each non-excluded column across the row when adding a column. Running sum of each non-excluded row down the column when adding a row.
ravg
Running avg of each non-excluded column across the row when adding a column. Running avg of each non-excluded row down the column when adding a row.
percentof
When adding a column, it calculates the total for each row and the overall total, then computes the value in the new column in this row as a percentage of the overall total. When adding a row, it calculates the total for each column and the overall total, then computes the value in the new row for each column as a percentage of the overall total.
=(c1+c2)/lc2
performs basic math functions based on the row and column values.

Row/column syntax is R, C, LR (last-row) and LC (last-column)
append
Concatenate all text and columns/rows together. Text can be surrounded by quotes or single quotes. Example: append('Text', c2, 'More Text', c3) add-label is a label to add in the first column when adding rows or the first row when adding columns.
rownum, colnum
Identifies the row number when adding a column, or the column number when adding a row.
datediff
Calculates the days between two dates.
dateadd
Calculates a new date N days from a date.

change-values

<ct:change-values>

<ct:format type="numeric" output="0.00" apply-to="columns:1,7-10" exclude="rows:1-2" />

<ct:format type="numeric" output="###,##0.00" apply-to="columns:2" exclude="rows:1-2" />

<ct:format type="numeric" output="0.000000E00" apply-to="rows:3" exclude="columns:1-2" />

<ct:format type="date" input="MM/dd/yyyy hh:mm:ss" output="yyyy-mm-dd" apply-to="columns:4" exclude="rows:1-2" />

<ct:replace input="oldname" output="new name" case-sensitive="true(default)|false" apply-to="columns:5" exclude="rows:1-2"/>

<ct:replace input="oldname1" output="new name1" case-sensitive="true(default)|false" apply-to="rows:5" exclude="columns:1-2"/>

<ct:replace input="oldname2" output="new name2" case-sensitive="true(default)|false" apply-to="columns:5" exclude="rows:1-2"/>

<ct:regular-expression match="old(.*)name" replace="new$1name" case-sensitive="true(default)|false" apply-to="columns:6" exclude="rows:1-2"/>

</ct:change-values>

Notes on change-values

Change values may be performed on any row or column. It allows the user to format the rows/columns to a desired form. It provides very powerful functionality in a simple easy to use form. Numeric format types are implementations of Java's NumberFormat Strings. Date format types are implementations of Java's DateFormat Strings. Note: if the input or output formats for date are not correctly defined, they will not work at all. Replace changes any complete occurrences of input in the whole cell with output. It does not do partial matching. input="E" output="Exceeds Expectations" will not replace the E in "onE". Regular-expression uses the Java regular expression handling to alter cells using regular expressions.

collapse-rows

<ct:collapse-rows exclude="" match="columns:1">

<ct:operation apply-to="columns:2" function="sum" />

<ct:operation apply-to="columns:3" function="avg" />

<ct:operation apply-to="columns:4" function="concat(,)" />

<ct:operation apply-to="columns:4" function="concat(<br/>)" />

</ct:collapse-rows>

Notes on collapse-rows

<ct:collapse-rows
The collapse-rows transform allows you to collapse multiple rows in to a single cell.

exclude - takes any of the columns syntax defined in filters. The excluded rows will appear unedited in the results.

match - takes any of the columns syntax defined in filters. The matched rows are the key used to combine rows.
<ct:operation
apply-to - Each column you wish to combine must have an operation tag. The apply-to attribute defines which column is going to be collapsed.

function - is how you want the rows to be collapsed.

Available Functions

sum
Sum all of the rows into one cell.
avg
Average all of the rows into one cell.
count
Count all occurrences of a value and put this count in the cell.
concat(d)
Concatenate the rows together into one cell using the delimiter d.
min
Selects the cell with the minimum (smallest) value.
max
Selects the cell with the maximum (largest) value.

Note: If the output is going into a table, you may want to use br instead. (Note syntax in example for br.)

column-reorder

<ct:column-reorder order="1,2,1,3,1,4"/>

Notes on column-reorder

Order is the new column order to return the datafunnel in.

Columns can be used multiple times as in the example, where the first column is referenced 3 times.

external-transform plugin

<ct:external-transform plugin="class:com.corda.demo.plugin" parameters="string"/>

Notes on using external-transform plugin

The current state of the datafunnel is passed to the plugin class specified as a two dimension array of strings. The plugin can manipulate the strings however it wants, add or remove and returns a two dimensional array of Strings. Callbacks are provided to the plugin to get values for request.variables, etc.

filter

Notes on filter

disable and enable take a command set such as rows|columns and disable or enable the specified rows or columns.
The disabled list is processed first and then the enabled. If you need to enable a list first and then disable some, you can create one filter to enable and follow it with another filter to disable. 1 is the first one.
rows|columns: the specified rows or columns will be disabled or enabled. 1 is the first one.
last-rows|last-columns: the specified rows or columns will be disabled counting from the end with 1 being the last one.
rows-like|columns-like: The MatchString is used to match the contents of the first column or row (respectively) and disable or enable the whole row or column as directed. See MatchString matches below.
rows-not-like|columns-not-like: The MatchString is used to find the contents that don't match in the first column or row (respectively) and disable or enable the whole row or column as directed. See MatchString matches below.
transpose puts columns into rows and vice-versa reverse-rows reverses the row order. If the value is set to all-but-first, then all rows except the first one are reversed.
MatchString matches for (rows-like|columns-like|rows-not-like|columns-not-like). MatchStrings are not case sensitive. "_" or "?" matches one char, "%" or "*" matches zero or more chars", "@" is the escape char.
Examples using the first row or column for the test.
rows-like:QTD* - will test the first column for the string "QTD" followed by 0 or more characters.
"QTD" can be in any case.
columns-not-like;*total* - will test the first row for the string "total" preceded and followed by 0 or more characters. "Total" can be in any case.
You can also specify a column or row address to use instead of the first one.
This can be done by telling which row or column to use for the test followed by the equals sign.
The options here are R for rows, C for columns, LR for last-rows and LC for last-columns. Examples using columns and rows for testing other than the first ones.
Reverse rows-like:C2=QTD* - will test the second column for the string "QTD" followed by 0 or more characters. "QTD" can be in any case.
columns-not-like;LR1*total* - will test the last row for the string "total" preceded and followed by 0 or more characters. "Total" can be in any case.

reverse-rows

<ct:reverse-rows exclude="rows|last-rows:1-2"/>

Notes on reverse-rows

Allows you to use syntax of (rows|last-rows|rows-like|rows-not-like) described in filter above.

If a row is excluded in the middle of a large datafunnel, e.g. 30, then the rows before it will be reversed in a block and the rows after it will be reversed in a block. So rows 1-29 will be reversed in a block and 31 - n will be reversed in a block. It will not be treated as one contiguous block where all but that row are reversed.

sort

<ct:sort exclude="rows|last-rows:1,2">

<ct:alpha-sort direction="(ascending)|descending" case-sensitive="(true)|false" apply-to="columns:1"/>

<ct:numeric-sort direction="(ascending)|descending" case-sensitive="(true)|false" apply-to="columns:1"/>

<ct:list-sort case-sensitive="(true)|false" include-all="(false)|true" zero-fill="columns: 1-4" list="outstanding;exceeds expectations;proficient;needs improvement" apply-to="columns:3"/>

<ct:date-sort format="yyyy-MM-dd hh:mm:ss" direction="(ascending)|descending" apply-to="columns:5"/>

</ct:sort>

Notes on sorting

exclude can take any arguments that enable and disable can in filters.
alpha-sort indicates an alpha sort.
numeric-sort will sort the values as numerics so 10 is after 2.
list-sort sorts items into the order of an ordered list. See include-all below for special behavior.
date-sort sorts items as date or time, based on the format string provided in format.
Sort method direction
- ascending - sorted in order from a-z or 0-9, etc.
- descending - sorted in order from z-a or 9-0, etc.
Sort method case-sensitive
- true - case will be considered while sorting. For Ascending A before a.
- false - case is not considered while sorting. For Ascending aA is before Ab.
Sort method apply-to specifies which ONE column will be used for the comparison.
Can take any of the column specifiers in filters: (columns|last-columns|columns-like|columns-not-like). However, only the first column specified in the list will be used as the comparison column. So specifying columns:2-5 would only use column 2, and a columns-like that matched columns 2,7 and 8 would only use column 2 as the comparison column. So this should only be used to specify one column.
Detail on using list-sort:
- list="item1;item2;item3;item4;item5" will sort the rows based on the occurrence of these values in the specified apply-to column.
- include-all is a special switch that allows you to make sure all the items are present even if they are not in the original datafunnel. This allows you to add missing rows into the datafunnel if they don't exist.
- zero-fill will fill the columns specified with zeros if they contain no data. This will only happen on new rows that are added as a result of turning include-all to true.
Detail on using date-sort:
- The format attribute is the date/time format string used to determine sort order.
- The order is years, months, days, hours, minutes and seconds. So 2005-01-01 will be before 2005-01-31 in ascending order.

fill-empty-cells

<ct:fill-empty-cells exclude="string" apply-to="string" fill-with="(previous-row)|specific-value" value="string" />

Notes on fill-empty-cells

Replaces empty cells with specific data or data from the previous row.

from-legacy

<ct:from-legacy last-column="string" add-header-row="(true)|false" />

Notes on from-legacy

Converts data from legacy form from the datafunnel to a group-item-value form.

to-legacy

<ct:to-legacy group="string" item="string" value="string" data-has-header="(true)|false" />

Notes on to-legacy

Converts data to legacy form from the datafunnel.