3 Replies Latest reply: Nov 12, 2016 11:21 PM by Arnaldo Sandoval RSS

    Create Timestamp for Incremental Load with no dates

    Joey Lutes

      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.

      Help?

        • Re: Create Timestamp for Incremental Load with no dates
          Marcus Sommer

          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
            Gysbert Wassenaar

            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.