Conceptual Question: Keeping History of Updated Values in SQLite - store as separate table?

@qdmt glad you liked the previous hints. You could just setup a SQLite database that would collect all the entries where you can then select the latest one per Listing with the use of a Windows/Rank function (the current driver bundled with KNIME would allow this function). The latest results (per Listing) will then be stored in a new table my_tracking_table_02.

The key in this case is to set up the SQLite table so that it will have an automated PRIMARY KEY and an automated TIMESTAMP. The other columns are dynamically constructed from the sample file with the help of a KNIME Flow Variable (v_sql_string2).

CREATE TABLE IF NOT EXISTS `my_tracking_table_01` (
    	ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
 ,    $${Sv_sql_string2}$$	
 ,    my_Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL
)

This is also like big data systems handle their data operations: just append all data and then select the latest one. You might have to check can handle the volume. Maybe you have to use one database per month/quarter/year. And also think about backups.

Maybe you can explore this example and see if this can help. Please note: SQLite does have a special handling of date and time variables.

2 Likes