Icon

justknimeit-32 - Change Structure of an Excel File

justknimeit-32 - Change Structure of an Excel File
Challenge 32: Change Structure of an Excel FileLevel: EasyDescription: A company buys their material from different manufacturers and stores allrelevant information in one excel file. In this challenge your goal is to change the structureof the excel sheet so that as little information as possible is stored repeatedly. Use as fewnodes as possible to change the structure of the excel file to the output shown below (oursolution has only 5 nodes, no scripting nodes, no components) Method 1: Direct & simple method using 5 nodes Method 2: Dynamic setting of chunk rows to loop and columns to be filtered at the end. Assumes that original rows are ordered and repetitive to allow correct row chunking. User only sets the columns to be fixed (e.g. year & month) Method 3: Dynamic rearrangement using collection columns, independent of the year & month combinations. User only sets the columns to be fixed (e.g. year & month) Read fileLoop every 6 rowsResets RowIDAppends columnsFilter repeat columns (year & month)Creates collection column for non-year&month columns (USER MODIFIES HERE)Group by unique year & month combinations, grouping other data into another collection listBreaks down the second collection columnSplits collection columns from othersSplits the collection column to its contentsLoops every collection columnAppends columnsAppends to year & month columnsExtracts column headersFitlers columns other than year & monthTransposeExtract column headers from the loopTransposeAppends Split Values to the original columns (creating a dictionary)Extract column headersTransposeRemoves iterative labelsReplaces Split Values with the column names according to the dictionarySplits rows with iterative labelsReattach the iterative labelsConcatenate rowsReplace column headers with the original headersRemoves iterative labelsSummarises Split Values columnReads fileFilters out the repetitive year & monthsRead fileSet chunks depending on number of rowsResets RowIDAppends columnsGroup by year & month(USER MODIFIES HERE)Counts unique year & month entriesCreate variablesExtracts all column headersExtracts variable of original column header listsSplits the lists of original column headersTransposeTransposeAppends all columns containing column headersTransposeSplits the iterative labelFilters the year & month columnsFilters all column containing the rest (including iterated ones)Concatenate both resultsCreates a list of column headers to keepCreates a variableExcel Reader Chunk Loop Start RowID Loop End (ColumnAppend) Column Filter Create CollectionColumn GroupBy Split CollectionColumn Column Splitter Split CollectionColumn Column ListLoop Start Loop End (ColumnAppend) Joiner ExtractColumn Header Column Filter Transpose ExtractColumn Header Transpose Column Appender ExtractColumn Header Transpose Cell Splitter Cell Replacer Row Splitter String Manipulation Concatenate Insert ColumnHeader Cell Splitter GroupBy Excel Reader Column Filter Excel Reader Chunk Loop Start RowID Loop End (ColumnAppend) GroupBy Extract TableDimension Table Columnto Variable ExtractColumn Header Variable toTable Row Split CollectionColumn Transpose Transpose Column Appender Transpose Cell Splitter ReferenceRow Filter ReferenceRow Filter Concatenate GroupBy Table Columnto Variable Challenge 32: Change Structure of an Excel FileLevel: EasyDescription: A company buys their material from different manufacturers and stores allrelevant information in one excel file. In this challenge your goal is to change the structureof the excel sheet so that as little information as possible is stored repeatedly. Use as fewnodes as possible to change the structure of the excel file to the output shown below (oursolution has only 5 nodes, no scripting nodes, no components) Method 1: Direct & simple method using 5 nodes Method 2: Dynamic setting of chunk rows to loop and columns to be filtered at the end. Assumes that original rows are ordered and repetitive to allow correct row chunking. User only sets the columns to be fixed (e.g. year & month) Method 3: Dynamic rearrangement using collection columns, independent of the year & month combinations. User only sets the columns to be fixed (e.g. year & month) Read fileLoop every 6 rowsResets RowIDAppends columnsFilter repeat columns (year & month)Creates collection column for non-year&month columns (USER MODIFIES HERE)Group by unique year & month combinations, grouping other data into another collection listBreaks down the second collection columnSplits collection columns from othersSplits the collection column to its contentsLoops every collection columnAppends columnsAppends to year & month columnsExtracts column headersFitlers columns other than year & monthTransposeExtract column headers from the loopTransposeAppends Split Values to the original columns (creating a dictionary)Extract column headersTransposeRemoves iterative labelsReplaces Split Values with the column names according to the dictionarySplits rows with iterative labelsReattach the iterative labelsConcatenate rowsReplace column headers with the original headersRemoves iterative labelsSummarises Split Values columnReads fileFilters out the repetitive year & monthsRead fileSet chunks depending on number of rowsResets RowIDAppends columnsGroup by year & month(USER MODIFIES HERE)Counts unique year & month entriesCreate variablesExtracts all column headersExtracts variable of original column header listsSplits the lists of original column headersTransposeTransposeAppends all columns containing column headersTransposeSplits the iterative labelFilters the year & month columnsFilters all column containing the rest (including iterated ones)Concatenate both resultsCreates a list of column headers to keepCreates a variableExcel Reader Chunk Loop Start RowID Loop End (ColumnAppend) Column Filter Create CollectionColumn GroupBy Split CollectionColumn Column Splitter Split CollectionColumn Column ListLoop Start Loop End (ColumnAppend) Joiner ExtractColumn Header Column Filter Transpose ExtractColumn Header Transpose Column Appender ExtractColumn Header Transpose Cell Splitter Cell Replacer Row Splitter String Manipulation Concatenate Insert ColumnHeader Cell Splitter GroupBy Excel Reader Column Filter Excel Reader Chunk Loop Start RowID Loop End (ColumnAppend) GroupBy Extract TableDimension Table Columnto Variable ExtractColumn Header Variable toTable Row Split CollectionColumn Transpose Transpose Column Appender Transpose Cell Splitter ReferenceRow Filter ReferenceRow Filter Concatenate GroupBy Table Columnto Variable

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: astl3
Created at: 2022-08-31
On NodePit since: 2022-09-03
Last update: 2024-04-27
Created with KNIME version: v4.6.1
Tags: justknimeit-32justknimeitexceldata processingdata preprocessingloopcolumn append

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

Try NodePit Runner!