Icon

02_​Data_​Merging_​Solution

Data Merging - Solution

Solution to exercise 2 for the KNIME Analytics Platform for Data Wranglers course
- Convert a column from number to string
- Concatenate data from two different sources
- Perform a lookup operation
- Join two database tables (using the DB Joiner node) and read database data into KNIME
- Join two local tables








Exercise 2: Join and ConcatenateIn this exercise you combine data by concatenating and joining tables and perform a lookup operation. 1a) Convert the "Newsletter" column from number to string in the table from the system 1 (Number to String node)1b) Concatenate the customer data from the two systems (Concatenate node)1c) Add a constant value column "Replacement" with the string value "unknown" to the emails to remove1d) Replace these emails with the value "unknown" in the customer information table (Cell Replacer node) 2a) Join the "TransactionsOnline" and "ProductNrAndPrize" tables directly on the database (DB Joiner node)2b) Read the joined table into KNIME (DB Reader node) 3) Add the store location information with each purchase in a store based on the StoreID (Joiner node) Product ID and PriceTransactions OnlineCustomer InformationSystem 1Store InformationCustomer InformationSystem 2Transactions StoreNewsletter to StringConnect to Databaseemails to removereplacement=unknownreplace the emails DB Table Selector DB Table Selector DB Joiner DB Reader DB Reader Concatenate CSV Reader Excel Reader Table Reader Table Reader Number To String SQLite Connector Joiner Table Creator ConstantValue Column Cell Replacer Exercise 2: Join and ConcatenateIn this exercise you combine data by concatenating and joining tables and perform a lookup operation. 1a) Convert the "Newsletter" column from number to string in the table from the system 1 (Number to String node)1b) Concatenate the customer data from the two systems (Concatenate node)1c) Add a constant value column "Replacement" with the string value "unknown" to the emails to remove1d) Replace these emails with the value "unknown" in the customer information table (Cell Replacer node) 2a) Join the "TransactionsOnline" and "ProductNrAndPrize" tables directly on the database (DB Joiner node)2b) Read the joined table into KNIME (DB Reader node) 3) Add the store location information with each purchase in a store based on the StoreID (Joiner node) Product ID and PriceTransactions OnlineCustomer InformationSystem 1Store InformationCustomer InformationSystem 2Transactions StoreNewsletter to StringConnect to Databaseemails to removereplacement=unknownreplace the emails DB Table Selector DB Table Selector DB Joiner DB Reader DB Reader Concatenate CSV Reader Excel Reader Table Reader Table Reader Number To String SQLite Connector Joiner Table Creator ConstantValue Column Cell Replacer

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: Maarit Laukkanen
Created at: 2019-10-18
On NodePit since: 2023-04-05
Last update: 2024-05-01
Created with KNIME version: v4.7.2
Tags: ETLconcatenatedatabasejoinpreprocessingeducation

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!