Step 1: Trim off the extra spaces, tabs and new lines in the string values. The operation is Transform > String > Trim. Select In Place. By doing this, we keep only the trimmed string values.

The input data is read from a server which may include extra spaces at the beginning and end. The date strings may look like the following. Therefore, we need to trim these spaces before parsing the date:

In the following table, the When field includes string representations of dates when a product was sold and the Value field shows the sale price. Later on we will introduce a Commission field, which is how much of the sale price goes directly to the sales person as commission.

The When field shows the output:

Step 2: Convert the strings from the When field into dates. The operation is Transform > String > To date. Select In Place. The new values may look the same as the trimmed strings, but these are now Date objects, which means we can manipulate them as dates rather than just as sequences of characters.

In the following table, the When field shows the output:

Step 3: Extract the month values from the Date field. The operation is Transform > Date > Month.

In the following table, a new field named "Index" is created to show the output:

Step 4: Show the months in long names from the When field. The operation is Transform > Date > Month name (long). Select In Place.

In the following table, the When field shows the output:

You will notice that the nulls in the When field and the Index field are still nulls (represented by empty cells). Unless the transform specifically processes nulls, all transforms will just pass nulls through unchanged. In the next step we will change those nulls into values we can work with.

Step 5: Set the Null values from the When field to "Unknown". The operation is Transform > String > Null to string. Select In Place.

In the following table, the When field shows the output:

Step 6: Sort the table by month index. The operation is Transform > Sort. Choose the Index field to sort by. Choose the Ascending order. Leave the Max Memory (MB) field blank. By doing this, this operation will sort the sales values without a maximum memory limit.

The following table shows the output:

Step 7: Create a new field named "Commission" in the original data source. Sum merge the sales values and commissions, and display a single total for each month. The operation is Transform > Reduce. Select the Sum action on the Value field and the Commission field. Leave the action blank on the other fields. By doing this, you can add up the sales values and commissions and view the total for each month. The rows have been merged down to one row per group, where the month names and indexes are the groups.

Alternatively, you can also sum retain on the sales values and commissions, and then perform a Discard Duplicates Transform to remove the redundant rows. You will receive the same output.

The following table shows the sorted table with the Commission field added:

The following table shows the output of the Reduce Transform:

Step 8: Subtract the commission from the sales value in each month. The operation is Transform > Decimal > Subtract. Specify ${@Commission} as the value to be subtracted. In this case we aren't subtracting a fixed amount (a static transform) but an amount read from another field - the Commission field. This is termed a dynamic transform, because the result requires input from more than one field.

In the following table, a new field named "Received" is created to show the output:

Now we can save the result of the transformations as a new data source for later processing. The transforms are repeatable, which means every month you can re-run the transforms to receive the most updated set of numbers, rather than create a sequence of transforms but run them only once.

String trim: remove whitespace at the beginning and end of date strings.

String to date: build date objects.

Date to month: extract month index from date objects.

Date to month name (long): replace date objects with long month names.

Null to string: set the null month names to “Unknown”.

Sort by month index: view the sales values throughout each month in the ascending order.

Reduce: sum merge the sales values and commissions, reduce the redundant rows and display a single total for each month.

Subtract: subtract the commissions from the sales values to calculate how much the company receives from sales each month.