Cheat Sheet: KNIME for Spreadsheet Users

Getting started with KNIME Analytics Platform VISUALIZATION FILTERING VALUE CREATION FLOW VARIABLES DATA TYPES &
• Use the Getting Started Guide to take your first steps with Bar Chart
Visualizes one or more aggregated Row Filter Filters rows in or out of the input
Math Formula
Implements a number of math operations across multiple Flow Variables allow for the parameterization of a CONVERSIONS
visual workflows at: [Link]/getting-started-guide metrics for different data partitions table according to a filtering rule. input columns. The math operations can be applied to workflow. A Flow Variables is a parameter that can
with rectangular bars where the The filtering rule can match a value multiple columns with the Math Formula (Multi Column) S String: Sequence of characters, e.g.
• Learn more about included nodes and explore working assume different values at different execution points in
heights are proportional to the in a selected column or numbers in node. the workflow & overwrite configuration settings in "This is a string"
examples in the KNIME Analytics Platform Version 5 Starter
metric values. The partitions are a numerical range. upcoming nodes. I Integer: Whole real valued number,
Perspective Collection on KNIME Community Hub. Renames selected Replaces values
defined by a categorical column. Column Renamer String Replacer e.g. -100 or 345
Filters columns in or out of the input columns according to the in a selected Creating a Flow Variable
Plots numerical values in data
Column Filter
table. Columns to be filtered can be D Double: Real valued number, e.g.
column name defined in string column if 1. Use a Configuration or a Widget
Node Action Bar: Interact directly with the columns (y-axis) against values in a manually chosen, selected -0.432 or 45.39
the dialog. Column names they match a node to create a Flow Variable at
node to, e.g., configure, execute, cancel or Line Plot
reference column (x-axis). Data according to their data type, or Date&Time: A data format for date,
must remain unique! defined pattern. any point in your workflow. 2. Use Cell Updater
Concatenate
reset a node. points are connected via colored based on a wildcard or regex any of the nodes converting data time, date&time, or date&time plus
Configure: Open the configuration dialog. lines. If the reference column on the expression matching their name. Cell Updater Updates a single cell of the input table with the value of into Flow Variables. time zone.
Execute: Executes the node. x-axis contains sorted time values, the specified flow variable. The cell to be updated must 3. Via the node configuration B Boolean: Two possible values only,
Cancel: Cancels the execution of the node. Top k Row Filter Sorts the input table according to a
the line plot graphically represents be specified via the row number and column name. The window in the Flow Variables tab, e.g. TRUE and FALSE
Reset: Resets the node. defined sorting criteria and keeps
the evolution of a time series. output table will be identical to the input table except for fill in a blank box with the name of Collection Cell: Collection of
Node Labels: Double click “Add comment” only the first k rows. In the
the single updated cell. the Flow Variable multiple values of either the same
below the node to add a comment/label. Plots multiple numerical data Advanced Settings tab, the output
order can be specified. Hidden Flow Variable Ports or different types e.g., can be a list
Add comment Dynamic ports: Additional input ports can columns on top of each other using Updates cells in the top input table with matching cells
Stacked Area Chart
Each node has two hidden Flow Variable ports to accept of values or a set of values. In a set
be added by clicking the plus on the left the previous line as the base Table Cropper Crops the input table based on the
Table Updater
from the bottom update table. A matching cell must have
incoming Flow Variables & to propagate them to the each value occurs only once.
side of the node. reference. The areas in between chosen row and column range. The the same column name and RowID in both tables.
lines are colored for easier Multiple cells of multiple rows and columns can be upcoming nodes. To make these ports visible, hover your Document/Image: KNIME Analytics
row range is defined via row number,
Not configured: Node is not yet configured and cannot comparison. This chart is updated. Additional rows and columns from the update cursor over the node. To configure a node’s flow variables Platform supports many more data
the column range either via column
be executed with its current settings commonly used to visualize table can be appended to the input table. right-click the node and select Configure flow variables. types like text documents, images,
name or column number.
trending topics. fingerprints, etc.
Configured: Node has been correctly configured and
Visualizes one aggregated metric Converts the data type of
may be executed at any time Pie Chart Quick node adding Preferences String to Number
for different data partitions with the selected columns from
Executed: Node has been successfully executed and Application Tabs & Info page string to either double or
colored slices on a circle where the S 2
results can be viewed and used in downstream nodes. areas are proportional to the Workflow Toolbar integer. Use the Number to
Error: The node has encountered an error during metric values. The partitions are String node for the
defined by a categorical column. Side panel opposite conversion.
execution.
navigation
Workflow Editor Parses the strings in the
Description selected columns
Node Repository according to a date/time
READ DATA DATE&TIME HANDLING MERGING Space Explorer format and converts them
Excel Reader
Reads content from sheets in
String to Date&Time
into Date&Time cells. Four
Extract Date&Time Extracts selected date and Concatenates the rows of all
Excel files (xlsx, xlsm, xlsb, and Fields Date&Time forms are
time fields from a selected input tables by writing them
xls format). Sheets and cells to supported: only date, only
column of type Date&Time below each other. Columns
be read can be defined in the Concatenate time, date & time, and date
and appends their values in with equal names are
configuration window. & time plus time zone. Use
new columns. concatenated. If one input table
Google Sheets the Date&Time to String
Reader
Shifts a selected date or time contains column names that
Reads data from a Google node for the opposite
with a defined duration or the other table does not, the
Sheets spreadsheet after Date&Time conversion.
Shift granularity. The shift value can columns can either be filled
authenticating with the Google with missing values (union) or
Authentication node. either be a duration column or
a numerical column. A positive filtered out (intersection).
Google
shift value is added to the Joins the columns of the two
Authenticates against
METANODES &
Authenticator Joiner

Google API services via selected date/time, a negative input tables based on one or
the "Authenticate" value will be subtracted. multiple joining columns. COMPONENTS
button's pop-up window. Calculates the difference Allows to select between
between two Date&Time different joiner modes. A Metanode or Component is a node
Microsoft
Authenticator
Connects to Microsoft Azure Date&Time objects, e.g., from two selected Adds matching values from a that contains other nodes.
Difference
and Office 365 cloud services columns, from a selected dictionary table to a data table Creating a Metanode or Component
MS via a number of interactive column and a fixed value, from a
Value Lookup
based on a lookup column. Select all relevant nodes, right-click and
Node port view
authentication options. selected column and the current When a lookup value matches select Create metanode for a
execution time, or from one cell an entry in the dictionary, the metanode or Create component for a
and the cell in the previous row selected cells are added to the component. Right-clicking a metanode
for a selected column. data table. Otherwise, missing or component opens the context menu
cells will be inserted. with a number of options such as
WRITE DATA expand or configure. To add input or
Excel Writer
Combines two or more output ports to a metanode or
tables by appending their On the entry page you have the option to:
Writes the input data table ORCHESTRATION Column Appender
columns according to the • Create a new workflow in your local space (i.e., the folder on
component click the plus on the left
into a spreadsheet of an side for additional input ports, and the
Excel file (xls or xlsx). Email Sender order of input tables. your computer that stores KNIME workflows), plus on the right side for additional
Sends HTML or plaintext emails Columns with identical • Open an existing workflow from your local space, output ports.
from an external SMTP server. column names will be • Connect to the KNIME Community Hub to find workflows,
Google Sheets
Writer Writes the input data table Attachments from the appended with "(#1)", "(#2)" nodes and components, and collaborate in spaces, Metanodes just collect nodes inside
filesystem may also be included. • E-Books: KNIME Advanced Luck covers and are an efficient way to clean up
into a new Google Sheets and so on. • Explore example workflows
advanced features & more. Practicing Data your workflow.
spreadsheet after
Science is a collection of data science case
authenticating with the CLEANING studies from past projects. Both available at Components encapsulate & abstract
Google Authentication node. functionality, can have their own dialog
DATA AGGREGATION Missing Value Defines and applies a strategy to [Link]/knimepress
and can have their own sophisticated,
Connects to Google Sheets, replace missing values in the input • KNIME Blog: Engaging topics, challenges,
Google Sheets
industry news, & knowledge nuggets at interactive views. They can be reused in
Connector given a Google API Splits the input table at the row that Aggregates numerical column based on one of table - either globally on all
Pivot Creates a pivot table by configuring Table Splitter Row Aggregator your own workflows but also shared
connection. Depending on the matches a given condition. The part of the following aggregation functions: columns, or individually for each [Link]/blog
columns for grouping and pivoting. with others: via KNIME Business Hub or
authentication method, the the table that occurred before the Occurrence count, sum, average, minimum, or column seperately. • E-Learning Courses: Take our free online
The group columns are turned into KNIME Community Hub. They can also
sheet should be either opened matching row is forwarded to the top maximum. Some aggregation functions support self-paced courses to learn about the different
unique rows, whereas the pivot Duplicate
Detects duplicate rows and applies represent web pages in a Data App
with a Google account or output table, the bottom output table weighting. Rows can optionally be grouped by a Row Filter steps in a data science project (with exercises &
values are turned into columns. the selected operation, e.g., deployed to others via KNIME Business
shared with a service account. contains the rest of the input table. category column. solutions to test your knowledge) at
Unpivot Stacks the cells of the selected removes duplicate rows. Duplicates [Link]/knime-self-paced-courses Hub. Flow Variables cannot enter or
Connects to a SharePoint Sorter Sorts the table in ascending or descending Aggregates column values for a defined are rows that have the same value exist a component, unless explicitly
value columns into one column. The • KNIME Community Hub: Browse and share
Online site and allows order based on the values of one or more Moving Aggregator moving window based on various in all selected columns. configured in the component’s input
cells of the selected remaining input workflows, nodes, and components. Add
downstream nodes to access columns. Additionally, string-compatible aggregation functions. The window length is and output nodes.
columns are appended to the Allows to compare values of two ratings, or comments to other workflows at
SharePoint Online
the document libraries as a file columns can be sorted in alphanumeric defined in the configuration dialog and can
Connector corresponding output rows. columns based on a defined [Link]
system, e.g., to read or write instead of lexicographic order. take any number from 2 to the maximum Column Merger
Splits values in the selected column primary and secondary column. The • KNIME Forum: Join our global community &
files and folders, or to perform number of rows in the table. The aggregation Metanode Component
Cell Splitter
into two or more substrings, as Cell Extractor Extracts the value of a single cell from the node outputs a new column where engage in conversations at [Link]
other file system operations. Column Combiner values are appended as new columns.
defined by a delimiter match. A input table and outputs it as a 1x1 table. the output value for each row will be • KNIME Business Hub : For team-based
The connection is closed when The row selection is defined via row Combines the content of a set of columns collaboration, automation, management, &
delimiter is a defined character, such the value in the primary column if it
the Connector node is reset, or number, the column selection either via row-wise and appends the concatenated deployment check out KNIME Business Hub at
as a comma, space, or any other is not missing, or the value in the
the workflow is closed. column name or column number. string as separate column to the input table. [Link]/knime-business-hub
character or character sequence. secondary column otherwise.
KNIME Press
Extend your KNIME knowledge with our collection of books from KNIME Press. For beginner and advanced users, through to those interested in specialty topics such as topic detection, data blending, and classic
solutions to common use cases using KNIME Analytics Platform - there’s something for everyone. Available for download at [Link]/knimepress.

Need help?
Contact us!

© 2023 KNIME AG. All rights reserved. The KNIME® trademark and logo and OPEN FOR INNOVATION® trademark are used by KNIME AG under license from KNIME GmbH, and are registered in the United States. KNIME® is also registered in Germany.