Icon

Fix missing dates - with missing first and last date

Fix missing dates

Interpolate missing dates using unix timestamps and missing values

Set a value for first and last dates if they are missing sample datawith some missingdatestakbb componentGenerate "unix seconds"columnlinear interpolationunix secondsback to newdate columnConvert Serial Secondsto Long datatypeget max and min dateNode 17set first and last dates(if missing) to start of previous monthand end of next month.We use the variable "LASTROWINDEX" created earlier to determine final row.Rule engine has a "ROWCOUNT" but, sincewe cannot perform calculations here, it isuseless to us in a rule as ROWINDEX starts at 0so will always be less than ROWCOUNT, evenon the final row! @takbb componentfirst day of month prior to min date@takbb componentlast day of month following max dateget row count so wecan deal with "final row"in Rule Engine.get last row indexcompatible with Rule Engineset back to a dateas Rule Engine coerces it into a StringTable Creator Date&Time toSerialNumber Missing Value UNIX Timestampto Date&Time Double To Int GroupBy Table Rowto Variable Rule Engine Date - RelativeFirst and Last Date - RelativeFirst and Last Extract TableDimension Math Formula(Variable) String to Date&Time Set a value for first and last dates if they are missing sample datawith some missingdatestakbb componentGenerate "unix seconds"columnlinear interpolationunix secondsback to newdate columnConvert Serial Secondsto Long datatypeget max and min dateNode 17set first and last dates(if missing) to start of previous monthand end of next month.We use the variable "LASTROWINDEX" created earlier to determine final row.Rule engine has a "ROWCOUNT" but, sincewe cannot perform calculations here, it isuseless to us in a rule as ROWINDEX starts at 0so will always be less than ROWCOUNT, evenon the final row! @takbb componentfirst day of month prior to min date@takbb componentlast day of month following max dateget row count so wecan deal with "final row"in Rule Engine.get last row indexcompatible with Rule Engineset back to a dateas Rule Engine coerces it into a StringTable Creator Date&Time toSerialNumber Missing Value UNIX Timestampto Date&Time Double To Int GroupBy Table Rowto Variable Rule Engine Date - RelativeFirst and Last Date - RelativeFirst and Last Extract TableDimension Math Formula(Variable) String to Date&Time

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: bbate
Created at: 2024-04-01
On NodePit since: 2024-04-03
Last update: 2024-04-30
Created with KNIME version: v4.7.8

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

Try NodePit Runner!