Icon

KNIME_​time_​series_​chunks

Date,PriceMark"Invested as 1 or 0Add sequence numberof previous row to enable self-joining to previousentrySort by Date ascendingAdd numericsequencenumberCall it "RowNum"Put prev "invested" flagon each rowWe are only interested in starts and ends of investment blocksso discard the "noise".We want to only keep rows where it is invested and either the prev or next row wasn't investedPut next "invested" flagon each rowSet PrevInvested to 0for row 1Each pair of lines represents the start and end of a blockso use this fact to generate a "block" numberwhich we can then group byFor each block,Get start and end datesand First and Last priceRename to tidy upSet nextinvestedfor final row.Note, that unlike "PrevInvested", I couldn't do this using a Rule Engine because we cannot determine final row, since ROWINDEX is zero-based and therefore never equates toROWCOUNT!!Find any isolatedinvestmentsThese are where an invest row has a non invest row on boths sidesThese need to be duplicated to provide a start and end for this "single-price" groupThis may return an empty table (if there aren't any isolated investments), which is perfectly fineDuplicate the additionalrowsSort by originalrow orderTable Creator Rule Engine Lag Column Sorter Counter Generation Column Rename Cell Replacer Rule-basedRow Filter Cell Replacer Rule Engine Math Formula GroupBy Column Rename Column Expressions Rule-basedRow Filter Concatenate Sorter Date,PriceMark"Invested as 1 or 0Add sequence numberof previous row to enable self-joining to previousentrySort by Date ascendingAdd numericsequencenumberCall it "RowNum"Put prev "invested" flagon each rowWe are only interested in starts and ends of investment blocksso discard the "noise".We want to only keep rows where it is invested and either the prev or next row wasn't investedPut next "invested" flagon each rowSet PrevInvested to 0for row 1Each pair of lines represents the start and end of a blockso use this fact to generate a "block" numberwhich we can then group byFor each block,Get start and end datesand First and Last priceRename to tidy upSet nextinvestedfor final row.Note, that unlike "PrevInvested", I couldn't do this using a Rule Engine because we cannot determine final row, since ROWINDEX is zero-based and therefore never equates toROWCOUNT!!Find any isolatedinvestmentsThese are where an invest row has a non invest row on boths sidesThese need to be duplicated to provide a start and end for this "single-price" groupThis may return an empty table (if there aren't any isolated investments), which is perfectly fineDuplicate the additionalrowsSort by originalrow orderTable Creator Rule Engine Lag Column Sorter Counter Generation Column Rename Cell Replacer Rule-basedRow Filter Cell Replacer Rule Engine Math Formula GroupBy Column Rename Column Expressions Rule-basedRow Filter Concatenate Sorter

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: @takbb Brian Bates
Created at: 2021-05-13
On NodePit since: 2022-02-06
Last update: 2024-04-27
Created with KNIME version: v4.3.2

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

Try NodePit Runner!