Icon

Set value on lagged row multi-column-conditions with adjacent rows (3)

Set value on lagged row column conditions with adjacet rows

Demonstration workflow to lag multiple columns to test adjacent rows and then apply rules based on the lagged columns

This pattern will "lag" a set of columns by joining ThisRowNumber to PreviousRowNumber and then ThisRowNumber to NextRowNumber.We tell it to keep ALL columns from the LEFT (upper data port) table and only a subset of columns from the RIGHT (lower data port) table in the join sequentialcounter to providesequentialrow identificationnewsample dataCounter for "Previous row"Counter for "Next row"Renamecounters asThisRowNumberPreviousRowNumberNextRowNumberJoin PreviousRow and keepspecific columnsrequired for conditionsJoin PreviousRow and keepspecific columnsrequired for conditionsFor a row to be of interest,it must always have the following matching:Entity IDCurrencyAmountTrans Subtypeand they must all have a value(not be missing) from current rowand Trans Subtype must be BUY or SELLApply conditionspecific remainingrulesSet Cleared to "No"for all rows initiallyJoin back to original data setSet cleared ifYesTidy upTidy upTidy up Counter Generation Table Creator Counter Generation Counter Generation Column Rename Joiner Joiner Rule-basedRow Filter Rule Engine Rule Engine Joiner Rule Engine Column Filter Sorter RowID This pattern will "lag" a set of columns by joining ThisRowNumber to PreviousRowNumber and then ThisRowNumber to NextRowNumber.We tell it to keep ALL columns from the LEFT (upper data port) table and only a subset of columns from the RIGHT (lower data port) table in the join sequentialcounter to providesequentialrow identificationnewsample dataCounter for "Previous row"Counter for "Next row"Renamecounters asThisRowNumberPreviousRowNumberNextRowNumberJoin PreviousRow and keepspecific columnsrequired for conditionsJoin PreviousRow and keepspecific columnsrequired for conditionsFor a row to be of interest,it must always have the following matching:Entity IDCurrencyAmountTrans Subtypeand they must all have a value(not be missing) from current rowand Trans Subtype must be BUY or SELLApply conditionspecific remainingrulesSet Cleared to "No"for all rows initiallyJoin back to original data setSet cleared ifYesTidy upTidy upTidy up Counter Generation Table Creator Counter Generation Counter Generation Column Rename Joiner Joiner Rule-basedRow Filter Rule Engine Rule Engine Joiner Rule Engine Column Filter Sorter RowID

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: brian
Created at: 2022-01-26
On NodePit since: 2022-02-25
Last update: 2024-04-27
Created with KNIME version: v4.5.0

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

Try NodePit Runner!