Concatenation, Lookups, and Joins

1. Concatenation, Lookups, and Joins

Welcome to Data Manipulation in KNIME! Data manipulation skills are essential in today's job market. In this video, you will learn how to shape and manipulate data using KNIME.

2. Welcome

I'm Philipp Kowalski and I have been using KNIME since 2018. I'm passionate about helping professionals streamline data analysis with KNIME.

3. Welcome

I am also a KNIME Certified Trainer.

4. Welcome

And I have received awards from KNIME for my contribution and my YouTube channel.

5. The CFO needs clarity

Here is your task: The CFO of Nova Goods needs your help. He asks you to merge financial data from multiple departments into a single report. KNIME lets you combine datasets with a few clicks. No more manual copy-pasting of spreadsheets. This video will teach you key techniques for that: concatenation, lookups, and joins. These will simplify the process and clarify things for your CFO.

6. Concatenating tables

Let's start with concatenation. Concatenation is a common way to merge data. It is ideal for tables with the same structure - identical columns but different rows. The Concatenate Node in KNIME stacks these datasets on top of each other.

7. Concatenating tables

For example, if you have sales data from many regions,

8. Concatenating tables

use KNIME to combine these tables into one dataset. Column names and data types should match across all tables. But the Concatenate Node can handle differences. It then replaces mismatched columns by using missing values.

9. Simple lookups

Next, let's look at the Value Lookup Node.

10. Simple lookups

This node enriches your data using a lookup table, even if the structures differ.

11. Simple lookups

Use this method to add information from a reference table using a common identifier. Imagine you have a table with sales transactions and one with product descriptions.

12. Simple lookups

You can use the product ID as the key to match product details to your sales data. Lookups handle one-to-one matches, while joins are good for more complex rules.

13. Merging with Joins

If you need more complex merges, the Joiner Node offers greater flexibility. It lets you merge datasets with different structures. This is done by matching fields and defining multiple criteria for matching.

14. Merging with Joins

The Joiner Node supports several types of joins: The Inner Join: This keeps only the matching rows.

15. Merging with Joins

The Left Outer Join: This keeps all rows from the left table and the matches from the right table.

16. Merging with Joins

The Right Outer Join: It keeps all rows from the right table and matches from the left.

17. Merging with Joins

Finally, the Full Outer Join combines all rows and adds missing values where no match is found. The Joiner node is ideal for complex merges that need flexibility in column matching.

18. Let's practice

You now know about concatenation, lookups, and joins. It's time to practice your skills.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.