Log Helper Function

In time in PHP programming we often did a "Helper" function. A helper does something genericly easier than the code provided by the application. For most parts we had a helper application that we used for logging certain errors into a database.

With QlikView I've started to setup a LogHelper to help log what goes on in my Collect scripts. This is hugely helpfull since it provides a log of how the load script have performed historicly, and I can easely see if there was a hickup at one of my clients.

Since QlikView lets you create subroutines that can be reused its rather obvious to create a few functions that cover out needs. However I very rarely see someone do this. Its really the one of the holy grails in QlikView.

So everytime I've collect data from a table I run this line:

CALL NewLogMessage('Table', QVDNOOFRECORDS('Table.qvd'));

One cool thing is that I Trace the output immediatly, so this also improves on how my Console output is.

Here is my code:

// Use this to store logmessages
// from load scripts
SUB NewLogMessage (_Key,_Value)
TRACE NewLogMessage $(_Key)=$(_Value);
Log: LOAD
'$(_Value)' AS Value,
'$(_Key)' AS Key,
NOW() AS Datetime
AUTOGENERATE 1;
// If table does not exist create it
IF NOT ISNULL(QvdCreateTime('LogHelper.qvd')) THEN
// Concatenate with old table,
// discard entries older than a month
CONCATENATE (Log) LOAD DISTINCT *
FROM LogHelper.qvd (qvd)
WHERE Datetime > ADDMONTHS(now(),-1);
END IF
// always tro to store it
STORE Log INTO LogHelper.qvd (qvd);
DROP TABLE Log;
END SUB

SUB GetLogTable (_Key)
IF NOT ISNULL(QvdCreateTime('LogHelper.qvd')) THEN
// If no parameter is given get all
// rows, else restrict it to requested table
IF ISNULL('$(_Key)') THEN
LET _Where = "WHERE (key = '$(_Key)')";
ELSE
LET _Where = "";
END IF
Log:
LOAD DISTINCT
Value,
Key,
Num(Date(Datetime)) as KEY_Date,
Time(Datetime) as KEY_Time
FROM LogHelper.qvd (qvd)
$(_Where);
ELSE
TRACE No log exists;
END IF
END SUB