When applying data transformation operations, there are a few very versatile nodes which probably cover all the operations you need: the String Manipulation node, Math Formula node, and Rule Engine node.
These nodes are very powerful and luckily share the same template for the configuration dialog. We have collected them into a single video to better show their similarities and differences.
Reference workflow is on the EXAMPLES server under:
EXAMPLES/02_ETL_Data_Manipulation/04_Transformation/02_StringManipulation_MathFormula_RuleEngine.
Exercise
Read the sales.csv dataset.
- Using the Rule Engine node, create a new column “currency” with value “USD” for the orders from the USA, and “EUR” for the orders from Germany.
- Using the Rule Engine node, create a new column “conversion” with value 1 if currency is “EUR”, and 0.88 if currency is “USD” (we refer to the exchange rate of Nov-04-2018).
- Using the Math Formula node, calculate values in a new column named “amount-in-EUR” by multiplying the value in column “amount” by the value in column “conversion”.
The product names are of type “prod_1”. Using the String Manipulation node, change the product names to “1”, “2”, and so on.
After reading the file, you’ll need two Rule Engine nodes.
Syntax for the first expression:
$country$ = "Germany" => "EUR"
TRUE => "USD"
Syntax for the second expression:
$currency$ = "USD" => 0.88
TRUE => 1
To calculate the “amount-in-EUR” column, you’ll need the following calculation in the configuration dialog of the Math Formula node:
$amount$*$conversion$
At the last step, you can make the product names to lowercase using this expression:
substr($product$, 5, 1)
You can download the solution workflow here.
* The link will open the workflow directly in KNIME Analytics Platform (requirements: Windows; KNIME Analytics Platform must be installed with the Installer version 3.2.0 or higher)
