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 :)