Data transformation for state timeline: keep last known value per column in new rows to prevent NULL

[saturnusdj]

MariaDB Query (simplified, format table):

SELECT
  n.created_at AS "time",
  o.id AS "product",
  n.data AS "data"
FROM
  db.o
  LEFT JOIN db.n ON o.id = n.parent_id
ORDER BY
  time;

Grafana table of pure query:

Transformations:

Transformation 3 is extra. It helps to work around the upcoming problem a little bit. Just forget it.
Transformation 5 is needed to apparently restore the field type.

The result as graph:

The issue becomes clear in the table:

New ‘time rows’ do not contain the current status of unchanged products and thus become NULL and empty in the graph, NULL is their new state.

It’s almost like Transformation 4 option “empty value” should have the option “Last non-NULL”.

How to solve this by either a different approach or fine tuning?

Edit: Setting T4 “Empty value” to “Null” and in graph “Connect null values” to “Always” helps to eliminated NULL in between values, but not to prevent NULL after the last value.

[saturnusdj]

Kick.

[yosiasz]

you are posting a request to fix something you have already implemented when it would be much better if you posted the business requirement with just plain English and sample data and expected result.

[saturnusdj]

Okay let’s try.

There is this db structure (given).

It contains a time column, a product column and a column that contains all kinds of JSON data, not limited to what is visible above.

A product goes through stages, visible in the data row 2-4: {"field":"stage","value":"STAGE"...}).
I would like to render this as a state timeline.