Generic Delta load functions for tables with timestamp and id
When loading data from many tables, and when its necessary to do more than the usual store and drop commands. You often end up with a lot of code that does stuff: Like fetching last timestamp for the load or storing the incremental data. And quite a lot traces to make its works correctly.
So I tend to write some functions for this which lets you save many line of code and make maintenance so much easier.
These functions should work generically for any tables that both have a modified date and an auto incrementing id fields.
First we need to set some general variables:
SET __LastExecTime = 0; Let __ExecTime = timestamp(Today(), 'YYYY-MM-DDXhh:mm:ss'); Set __DeltaPath = '..\Data\ERP\Delta\';
I use 2x underscores prefix for variable names that should only be used in the load script. And single underscore prefix for variables I use in functions.
LastExecTime is set to 0, because for each table we load, we need to know its previous value, or if its the first time then its need to be in the beginning of time.
ExecTime we set to now, we use this to discard records that could be created while we are loading data. If we don't do this we risk getting some orphans. Rows that don't have their relations.
DeltaPath is where on the filesystem data should be stored.
The first subroutine we use is GetModifiedData, which is used to figure out what date we can use to define our delta.
Sub GetModifiedDate(_Filepath,_DateField) Let _CreateTime = QVDCreateTime('$(_Filepath)'); Trace GetModifiedDate: $(_Filepath), $(_DateField), $(_CreateTime); // If a file already exist if not isnull(QVDCreateTime('$(_Filepath)')) then Trace QVD Exist at $(_Filepath); // Then get the highest timestamp from it LoadTime: Load Max($(_DateField)) as LastModifiedDate From $(_Filepath) (qvd); Let __LastExecTime = timestamp(peek('LastModifiedDate',0,'LoadTime') ,'YYYY-MM-DDXhh:mm:ss'); Drop Table LoadTime; Else Trace Nofile at $(_Filepath); // Reset timestamp LET __LastExecTime = timestamp(0,'YYYY-MM-DDXhh:mm:ss'); End if End Sub
The second is StoreQVD and this we use to store our Data with. For both routines I have added a lot of Trace's, since I need more information than QlikViews dialogs provide to make sure this works as expected:
Sub StoreQVD (_Filepath,_IdField,_Table) Let _Rows = QvdNoOfRecords ('$(_Filepath)'); Trace StoreQVD: $(_Filepath), $(_IdField), $(_Table), $(_RowCount); if Not isnull(QvdCreateTime('$(_Filepath)')) then Trace Contatenating; Concatenate ($(_Table)) LOAD * From $(_Filepath) (qvd) Where Not Exists ($(_IdField),$(_IdField)); end if If NoOfRows('$(_Table)') > 0 AND ScriptErrorCount = 0 Then Store $(_Table) Into $(_Filepath); Let _RowsFinal = QvdNoOfRecords ('$(_Filepath)'); Trace Stored file before: $(_Rows), after: $(_RowsFinal); Else Trace No file stored ($(_Filepath)); End If Drop Table $(_Table); End Sub
Finally you can wrap these two functions around yor regular collect statements like this:
Call GetModifiedDate('$(__DeltaPath)Transaction.qvd' ,'ModifiedDate'); Transaction: SQL SELECT Amount, Id, ModifiedDate FROM Transaction__c WHERE ModifiedDate >=$(__LastExecTime) and ModifiedDate < $(__ExecTime) Order by Id; Call StoreQVD('$(__DeltaPath)Transaction.qvd' ,'Id' ,'Transaction'); Call GetModifiedDate('$(__DeltaPath)TransactionLine.qvd' ,'ModifiedDate');
TransactionLine: SQL SELECT LineAmount, Id, ModifiedDate FROM Transaction__c WHERE ModifiedDate >=$(__LastExecTime) and ModifiedDate < $(__ExecTime) Order by Id; Call StoreQVD('$(__DeltaPath)TransactionLine.qvd' ,'Id' ,'TransactionLine');
And so on. The idea is really to write generic code, that dramatically lowers the amount of lines you need and keep the tricky stuff centrally handled. In this example we have saved about 40 lines pr. table we need to fetch!
Hope you can use this :)