Here’s a fun Friday post.
As I was updating my incremental load materials for the upcoming October/November Masters Summit for Qlik, I ran across an old note about creating “Attributes” in QVDs using Tags. Due to a bug in QVD generation I couldn’t implement the idea at the time. But the bug is long since fixed so here I present the general idea of “QVD Attributes” (my name, not an official Qlik thing).
We can assign Tags to Qlik Tables using the script TAG statement:
Tag TABLE TranData with 'MaxId=$(vMaxId)';
Tag TABLE TranData with 'Source=Salesforce';
If the table “TranData” is subsequently written to a QVD using the STORE statement, the tag will be included in the QVD file.
We can read tags directly from a QVD with script:
LOAD String%Table as Tag
FROM [lib://mylib/TranData.qvd]
(XmlSimple, table is [QvdTableHeader/TableTags/String]);
Note that we are reading only the XML header from the beginning of the file. This is very fast.
If we use a structured tag format like “key=value” we can create a generalized “Attribute” store in a QVD that can tell us something about the contents of that QVD. That could be useful when we want to use the QVD in a subsequent script.
How do we retrieve these tags in a useful and consistent way? I think loading them as variables would be a good approach. We can create a reusable subroutine to load all “key=value” tags from a QVD and generate variables named “Tag:key”. We then call the Sub, using the path to any QVD:
Call LoadTags('lib://QVDFiles/TranData.qvd');
We might utilize the new variables in something like an incremental Load.
SQL SELECT * From Trans Where Id > $(Tag:MaxId);
Or as a control to conditionally execute a script block.
If '$(Tag:Source)' = 'Salesforce' Then
// Use the Salesforce Load statement
EndIf
What is all this stuff? Subroutines? Preceding Load? Incremental Load? Tags? Join me at the Masters Summit for Qlik in Dublin or Orlando where we will teach you all about these topics and make you a scripting Master.
At the bottom of this post is a complete sample including the subroutine. You only need to change the “QVDFiles” to your lib to run this on your system.
-Rob
// Subroutine to Load Tagname=Value pairs from a QVD as "Tag:Tagname" Variables.
Sub LoadTags(_path)
_TempTags:
Load
SubField(Tag, '=', 1) as TagName,
SubField(Tag, '=', 2) as TagValue
;
LOAD String%Table as Tag
FROM [$(_path)]
(XmlSimple, table is [QvdTableHeader/TableTags/String])
Where String%Table like '*=*';
For _i = 0 to NoOfRows('_TempTags') - 1
Let _vname = 'Tag:' & Peek('TagName', $(_i), '_TempTags');
Let [$(_vname)] = Peek('TagValue', $(_i), '_TempTags');
Next _i
Set _vname=;
Set _i=;
Drop Table _TempTags;
End Sub
// END of Subroutine
// Generate some sample data
TranData:
LOAD
RecNo()*2 as Id,
Rand() as Amount,
'A' as Customer
AutoGenerate 6;
// Something we may want to record from the data is the max Id value, for later incremental load.
TempId:
LOAD Max(Id) as MaxId Resident TranData;
Let vMaxId = Peek('MaxId');
Drop Table TempId;
// We can tag with $(variable) values or literal values.
Tag TABLE TranData with 'MaxId=$(vMaxId)';
Tag TABLE TranData with 'Source=Salesforce';
// Some other tag that is not part of our name=value scheme.
Tag TABLE TranData with "ABC";
// STORE and DROP the QVD
Store TranData Into [lib://QVDFiles/TranData.qvd] (qvd);
Drop Table TranData;
// Call our subroutine to load tags as variables, from the disk QVD.
Call LoadTags('lib://QVDFiles/TranData.qvd');
// Demonstrate how we might use these tag values.
// In a SQL Where clause.
SET select = SQL SELECT * From Trans Where Id > $(Tag:MaxId);
Trace $(select);
// In a conditional to select a block of script.
If '$(Tag:Source)' = 'Salesforce' Then
Trace Going to use the Salesforce load statement;
EndIf
