Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.


Create Timestamp for Incremental Load with no dates

I'm rephrasing a question I'd asked earlier which I believe I had asked in error.

I have multiple tables (ALL Dimension, NO Fact) that do not contain date fields.  This is an incremental load process where the end result is to show a timeline of the Slowly Changing Dimensions contained in the tables.  I have implemented SCD Type 2 and it is working correctly using Hash256 ( thanks barryharmsen ) and adding new data exactly how I require it on each iteration - BUT:

I need a timestamp.  In essense, I need to know when each row was added/loaded to the growing qvd.  This timestamp will be a common field from which I will build a calendar and can report from.

Obviously if I add a timestamp field to each table load statement (which I tried), I get a synthetic key (which I do not have without it).  For each table and row, I need to know its load date/time so that I can build timeline charts and show a temporal view of the changes in dimensions.

Ideas?  I've thought about a link table, but am not sure exactly how I would go about implementing that.  I've scoured the Qlik Community and read dozens of articles on this topic to no avail.


3 Replies

Re: Create Timestamp for Incremental Load with no dates

Now I understand better what do you want to do. Ok. you need the timestamp - probably the date from them will be enough as key and the time-part could be removed or separated as additionally information or maybe linked per separated master timetable - but I think you don't need to link the different timestamp-fields together else keep them dimension-table and the related master-calendar as separated datamodels. A link-table approach with a canonical calendar might be possible but I don't see the added value for them. Take a look here:

Canonical Date

Why You sometimes should Load a Master Table several times

The Master Time Table

- Marcus

Re: Create Timestamp for Incremental Load with no dates

I think I'd add a timestamp to each dimension table and qualify it so the fields don't result in a synthetic key. You can use those fields to show timelines for each dimension. You can create a data island calendar table or just use variables to select a period. You can use set analysis expressions to filter the values of the timestamp values in chart expressions using those variables to make each chart show the same period.

You just won't be able to show multiple dimensions in the same chart/table object. If you need that then you need to create additional tables that are not associated (linked) to your fact table. Perhaps that's an easier option. Your fact table doesn't have a timestamp field anyway. You can then simply concatenate all your dimension tables into a new table and add a timestamp field in that new table at the same time. Qualify the fields in that table to prevent synthetic keys.

talk is cheap, supply exceeds demand
Contributor III

Re: Create Timestamp for Incremental Load with no dates


I agreed with Gysbert suggestion regarding qualifying your time-stamp columns (first paragraph), I suggested that approach in your other thread.

I do not extend my reply on how to consume these timestamps because there is not a clear definition on how you want to implement this logic, e.g. a part of your current application or in a different application.


Community Browser