KNIME 101 - ETL Tools for Beginners

Reader nodes help to read lines of text from files.

In this video, we will import our data using:

Excel Reader: Allows to access data from an Excel file in KNIME.

File Reader: Allows to read the content of the file (ASCII file/URL location)

CSV Reader: Reads CSV files

Writer nodes allows us to write to local file system paths, remote URLs, Knime URLs.

In this video, we will learn how to write data from one file and save it to another using Excel Writer and CSV Writer nodes.

It allows manual creation of a data table.

In this video, we will be creating a table with hard-coded input.

Joins are used to combine data or rows from two or more tables based on a common field between them.

In this video, we will learn several join techniques and will be able to combine or join data in the following types:

• Left Outer

• Right Outer

• Full Outer

• Inner

• Left Anti

• Right Anti

This node joins two tables together on one or more common key values.

In this video, we will be performing the following Joins:

Inner, Left Outer, Right Outer, Full Outer

It rename column names or change the column types by picking one of the possible options.

In this video, we will perform the following 2 operations:

• Splitting one column into two

• Change the name of the column headers

Similar to the Joiner function but can be faster for large tables. This node helps to paste columns side-by-side where the number of rows must be the same.

In this video, we will learn to append the columns of the second table to the first table.

It concatenates the values from two or more columns and displays the result into a new appended column.

In this video, we will be concatenating two or more columns into a newly appended column.

Allows to select two or more columns that you need to merge.

In this video, we will:

• Merge columns to create a new column

• Replace columns

This node identifies duplicate values in certain columns.

In this video, we will be looking into the following options:

• Remove duplicate rows

• Keep duplicate rows

It includes rows that match certain criteria. Rows that are not matched will be eliminated.

In this video, we will be performing the following tasks:

• Find out list of Male/Female candidates (Pattern Matching Criteria)

• List of people working in Finance Department (using Nominal Value Row Filter)

• Filter out people whose credit score exceed a certain limit (Range Checking Criteria)

This node deals with the complex conditions in the filtering rule. This node only applies to a nominal attribute (String, Columns).

In this video, we will display the list of all people from particular department (Finance, HR, General Admin).

Similar to Row Filter node but works based on user-defined rules.

In this video, we will look into the following conditions:

• Condition 1: Filter data of all people with location name starts with "Br" and having FICO scores > 600

• Condition 2: People working in HR department who do not fall under "Condition 1"

This node extracts all the rows if the time value of the selected column falls under the given time window of the input.

In this video, we will:

• Fetch number of employees joined within specific date range

• Display the employee details who joined before 30 days of a particular date

This node removes one or more unnecessary columns from the input data table.

In this video, we will be:

• Filtering column using Manula selection, Wildcard/Regex selection, Type selection

• Extracting those column which has the word "salary" into it

Splitter nodes are used to split data into smaller chunks for processing in small batches.

In this video, we will learn about the following Splitter nodes:

• Row Splitter: This node has the same functionality as the Row Filter node, except, it has an additional output that displays the rows that are filtered out.

• Rule-based Row Splitter: This node tries to match a list of user-defined rules with the rows presents in the input table in the defined order.

• Nominal Value Row Splitter: This node splits the rows based on the selected value of a nominal attribute.

Sorts the rows based on user-defined criteria.

In this video we will be sorting different columns value by:

• Ascending/Decending order

• Alphabetical/Reverse Alphabetical order

Changes the sequence of the input columns based on user defined rules. It does not sort the values inside the columns.

In this video, we will change the column sequence in which they are sorted.

A pivot table reorganizes selected columns and rows of data in a table. It also enables you to categorize, breakdown, or filter data.

In this video, we will cover the following topics:

• Group totals

• Pivot table

• Manual Aggregation

• Pivot totals

The GroupBy node performs grouping of data by the unique values in the selected group columns.

In this video, we will find out:

• The count of people and their salaries working in a particular department

• Department wise list of people separated by a comma

This node helps to transform the data from matrix (pivot table) to column (lined item). It also duplicates the remaining input columns by appending them to corresponding output row.

In this video, we will be:

• Normalizing data (cross tab to flat file) using Manual and Wildcard/Regex selection

• Renaming columns

• Change the sequence of the columns

This node concatenates two tables with equal names. Column names that do not match can either be filled with missing values or filtered out.

In this video will we be concatenate columns using the following options:

• Append Suffix

• Using Union of columns

• Using Intersection of columns

The Missing Value node helps to handle missing values in data tables.

In this video, we will find out:

• The missing value with certain logic (Mean and Maximum Value)

• The missing value with correct department name (Previous Value)

This node allows to split the values of a selected column into parts using a user-specified delimiter character.

In this video, we will perform the following:

• Split the name into two parts, that is, First name and Last name

• Change the column names

This node splits the values of a selected column into several separate new columns.

In this video, we will learn to split the Tax ID code into three different columns.

Aggregates the cells of the selected column groups per row using the selected aggregation method. It also allows you to perform mathematical operations on multiple columns at the same time.

In this video, we will:

• Calculate the standard deviation of the salaries (3 years) in desc order

• Concatenating salary columns into one

Data Type conversion refers to changing an entity of one datatype into another.

In this video, we will learn the following data type conversions:

• String To Numbers: This node converts strings in a column to numbers

• Number To String: This node helps to converts numbers in a column to strings

• String to Date&Time: This node parses and converts the strings in the selected String column into date/time cells

Extracts the selected column fields (Local Date, Local Time, Local Date-Time, and more) and appends their values as corresponding integer or string columns.

In this video, we will be extracting the followings:

• Year

• Quarter

• Month (Name)

• Month (Number)

• Day of Year

• Day of Month

• Day of Week (Name)

Math Formula node is used to perform mathematical computations in KNIME.

Rule Engine is used to create logical statements.

In this video, we will be solving the following problem statements:

• Average of two test scores (Math Formula)

• Score Status (High, Medium, Low)

• Diff between 2 test scores (Math Formula)

• Recheck status if scores diff is high

• Interview call ("Analytics" using LIKE, Avg Score >= 60)

• No Action (Rule Engine)

String manipulation or handling is the process of manipulating strings like search and replace, capitalize, or, remove leading and trailing white spaces.

In this video, we will learn:

• To extract the year from the file path

• To extract the month name from the file path

• To convert the string to Integer

• IndexOf() function

• Substr() function

String manipulation or handling is the process of manipulating strings like search and replace, capitalize, or, remove leading and trailing white spaces.

In this video, we will see other uses of String Manipulation, that is, capitalize / change cases, concatenate, convert type, len, extract, replace, and more.

This node allows you to add new columns to a table, or replace existing columns using expressions that are executed row-wise.

Column Expressions = String Manipulation + Rule Engine + Math Formula

In this video, we will:

• Find out the average of the 2 test scores

• Classify the scores into low medium and high

• Find the diff between the 2 test scores

• Convert names into uppercase

In this video, we will be using annotations, comments, and metanodes to help you streamline your workflow and to communicate better.

The Statistics node gives you basic summary information about numeric fields.

It also calculates statistical moments such as mean, minimum, maximum, standard deviation, variance, median, and more.

In this video, we will learn the basics of statistics node as follows:

• Creating Statistics table (Numeric data)

• Creating Nominal Histogram table (Non-numeric data)