Hands-on Exercise_​karl

Step 2.1. Clean the Data

Clean the Customer Information System 1 data

1. Exclude the first row and first column with the Table Cropper node

2. Remove rows with duplicate CustomerID with the Duplicate Row Filter node

3. When the age is missing, insert the mean age of the dataset with the Missing Value node

Step 1.2. Access the Data from a Database

1. Connect to the database with the SQLite Connector node

2. With two DBTable Selector nodes, select the tables

   • Transactions

   • ProductNrAndPrice

3. Read the tables with two DB Reader nodes

Clean and Standardize the Second Customer Dataset

This metanode repeats the same preparation steps used for the first customer file, but on Customer Information System 2. It cleans the records, fixes missing or inconsistent values, and applies the same data transformations so both customer datasets end up in a matching structure and can be combined reliably later.

Step 5.2. Aggregate the Data II
Summarize Sales by Product

Groups the joined transaction data by product and calculates summary values, turning many individual purchase records into a compact table that shows overall performance for each product.
Create a Product Summary Pivot

Reorganizes the grouped sales summary into a pivot table, turning the product-level results into a compact report for easier comparison. This helps present the aggregated data in a clearer, more analysis-friendly layout.

Step 3.1. Transform the Data

1. Create a new column AgeGroup with values Adolescent, Adult, Older Adult according to the age value: <18 Adolescents, 18-65 Adults, >65 Older Adults

2. Replace the character "_" with a space " " from the column country Country

3. Split the column CustomerID on the character "_" with the Cell Splitter node

4. Rename the columns created by the split with the Column Renamer node

5. Merge the columns Email and CorporateEmail with the Column Merger node. Keep the Email when both are available.

6. Convert the column Newsletter with the Number to String node