Icon

20200331_​PIKAIROS_​SUBSTRACT_​BETWEEN_​TWO_​DIFFERENT_​TABLES

This workflow shows how to achieve first a cumulative sum of column values and then a substraction of two tables, while preserving the column names of one of the tables (the reference)

incomingchurningRemove Headerto maketables comparableRemove Headerto maketables comparableReplacemissing valuesby zerosReplacemissing valuesby zerosTranspose tablefirst to calculatea cumulative sumon rowsTranspose tablefirst to calculatea cumulative sumon rowsWork out cumulativesum of rows valuesTranspose tableagain to go backto original table structureTranspose tableagain to go backto original table structureWork out cumulativesum of rows valuesNegate valuesto achieve latera substraction(later GroupBycannot achievesubstraction butonly addition)Concatenatehere the two tablesto achieve laterthe substraction of bothGroup byrow index andaggregate by additionto achievesubstractionCreate new columnwith row positionCreate new columnwith row indexRestituteoriginalcolumn headerTransposecolumn headersExtract columnheadersto use it laterTransposecolumn headersAppend thetwo column headers,the old ones andthe new onesremove twofirst columnsremove twofirst columnsRemove not anymoreneeded row index columnAppend Year& MonthpreviouslyremovedThat's all folks ! Table Creator Table Creator ExtractColumn Header ExtractColumn Header Missing Value Missing Value Transpose Transpose Moving Aggregation Transpose Transpose Moving Aggregation Math Formula(Multi Column) Concatenate GroupBy Math Formula Math Formula Insert ColumnHeader Transpose ExtractColumn Header Transpose Column Appender Column Splitter ReferenceColumn Splitter Column Filter Column Appender InteractiveTable (local) incomingchurningRemove Headerto maketables comparableRemove Headerto maketables comparableReplacemissing valuesby zerosReplacemissing valuesby zerosTranspose tablefirst to calculatea cumulative sumon rowsTranspose tablefirst to calculatea cumulative sumon rowsWork out cumulativesum of rows valuesTranspose tableagain to go backto original table structureTranspose tableagain to go backto original table structureWork out cumulativesum of rows valuesNegate valuesto achieve latera substraction(later GroupBycannot achievesubstraction butonly addition)Concatenatehere the two tablesto achieve laterthe substraction of bothGroup byrow index andaggregate by additionto achievesubstractionCreate new columnwith row positionCreate new columnwith row indexRestituteoriginalcolumn headerTransposecolumn headersExtract columnheadersto use it laterTransposecolumn headersAppend thetwo column headers,the old ones andthe new onesremove twofirst columnsremove twofirst columnsRemove not anymoreneeded row index columnAppend Year& MonthpreviouslyremovedThat's all folks ! Table Creator Table Creator ExtractColumn Header ExtractColumn Header Missing Value Missing Value Transpose Transpose Moving Aggregation Transpose Transpose Moving Aggregation Math Formula(Multi Column) Concatenate GroupBy Math Formula Math Formula Insert ColumnHeader Transpose ExtractColumn Header Transpose Column Appender Column Splitter ReferenceColumn Splitter Column Filter Column Appender InteractiveTable (local)

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: Molina
Created at: 2020-03-31
On NodePit since: 2022-02-06
Last update: 2024-04-26
Created with KNIME version: v4.0.2
Tags: Moving aggregation math formula multi column transpose

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

Try NodePit Runner!