In-fill missing value

KNIME Analytics Platform
1

Hello,

I have a time stamp table with a repeating sequence column (e.g. 90, 91, 92, 93 and repeat), some of the sequences are missing.

I am trying to find the missing value and insert a new row with that value in the column

any help would be appreciated

thanks,

2

Could you provide more detail about your data and preferably provide an example of the existing data and the new table you’d like to create?

1 Like
3

thanks for the reply

the data looks like this:

DateTime Machine# Mode Sequence
2026-01-05T07:23:00.261 GSB01 93
2026-01-05T07:23:39.479 GSB01 90
2026-01-05T07:25:03.896 GSB01 91
2026-01-05T07:37:14.519 GSB01 92
2026-01-05T07:40:43.126 GSB01 93
2026-01-06T09:49:12.376 GSB01 91
2026-01-06T10:00:01.121 GSB01 92
2026-01-06T10:02:01.825 GSB01 93
2026-01-06T10:12:41.143 GSB01 90
2026-01-06T10:14:41.883 GSB01 91
2026-01-06T10:26:42.770 GSB01 92
the mode sequence should be 90, 91, 92, 93 and repeats

I am trying to find the missing mode sequence and insert a row with the missing sequence value

DateTime Machine# Mode Sequence
2026-01-05T07:23:00.261 GSB01 93
2026-01-05T07:23:39.479 GSB01 90
2026-01-05T07:25:03.896 GSB01 91
2026-01-05T07:37:14.519 GSB01 92
2026-01-05T07:40:43.126 GSB01 93
90
2026-01-06T09:49:12.376 GSB01 91
2026-01-06T10:00:01.121 GSB01 92
2026-01-06T10:02:01.825 GSB01 93
2026-01-06T10:12:41.143 GSB01 90
2026-01-06T10:14:41.883 GSB01 91
2026-01-06T10:26:42.770 GSB01 92

thanks,

4

depends on the size of the table and the flexibility that you need but there are different approaches:

A)
you can iteratively process your table and check top to bottom for the first mismatch between the row number/index against a modulo of your sequence size. this will essentially process the table to top bottom until the first mismatch, correct it and then start the next loop again going top to bottom. use a table splitter and row inserter and table concat when it found a mismatch

B)
if your table size is smaller or you have plenty of processing power, you can also cross-join your sequence against your table and then use e.g. the Column Expression Node with activated Window Access to write a logical check for each row if it is the original one, a row to discard because it was created by the cross join unnecessarily but a record for that sequence element exists already, or lastly a new addition (for which you may then still need to null the other columns except for your sequence

C)
if your sequence is purely numeric, you can also use the Column Expression node with window function, or the moving average / moving aggregation nodes to make a delta to your previous row. whenever your delta n>1 you will need to add n-1 rows of your sequence. and if your delta is negative but not -90 you will need to add a row. issue here is that you should strictly go by set to avoid double entries from double insertions.

5

Thanks for the reply

the sequence is strictly numeric, but the table is quite large

I am leaning towards solution A but don’t know how and what nodes to use.

thanks,

6

Yeah, the Missing Value node is perfect for this.
Just connect it after your data source, choose the column(s) you want to fill, and set the method to “Previous value” (forward fill) or “Next value” (backward fill). You can also combine both if needed.
It works reliably on string and numeric columns. If your missing values are scattered, run it once for forward and once for backward and then use a Rule Engine or Column Combiner to pick the best result.
Super simple and no scripting required. Let me know if your data has any special pattern.

7

you skipped the difficult part about inserting empty rows at the right places.

8

Hi Richter

it is not a missing value problem, it is a missing row problem

i am trying to insert a row if a pattern is not complete

the pattern is 90, 91, 92, 93

9

Hi @Karim_Ibrahim
In the sequence: 90,91,92,93 is there always one number missing? Or is it possible that more than one number is missing in the sequence?

gr. Hans

10

Hi HansS

it is possible to have more than one number missing in the sequence

1 Like
11

Hi,

in_fill.knwf (100,9 Ko)

This wf seems to work : the idea is to to create a table for specification (ie sequence expected and for each item his successor) then parse the input table for each row and add what is necessary. This can be heavy if your input data are big !
Hope this is usefull !
Best,

Joël

1 Like
12

Thank you Joel

for some reason, your table creator node doesn’t work with my knime installation version which is the latest

so i cannot test the work flow. also, i have a big input data table

1 Like
13

Strange, I have the lastest one too 5.11.0.

Here are tables : input and some light ones.

image
image1716Ă—968 168 KB

Best,

Joël

1 Like
14

Thank you very much!

i am reinstalling the whole application and will test

1 Like
15

There’s a known bug with the Table Creator node in 5.11.0. I think it depends on whether you have the chem extension installed.

error
error520Ă—324 39.1 KB

Here’s another approach, although it requires some manual adjusting. It reproduces the output you posted, although I doubt that it will work with a large arbitrarily configured input.
Insert Missing Values in Sequence.knwf (126.8 KB)
workflow
workflow1815Ă—417 67.3 KB

table
table770Ă—619 127 KB

2 Likes
16

Thank you for the reply.

I found a solution using Power Query DAX in excel. I will have to write to CSV and read from CSV for further processing.

the code from DAX ran very fast on large sets of data

3 Likes