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
Here’s a great pre-made library and accompanying blog post about using tags for incremental loads. I’ve found it to be highly efficient and reliable.
https://github.com/mikegarciam/DeltaTags
disclaimer: I’m the author.
Really nice!
Hi Rob & Miguel,
Inspired by Miguel’s great work with Lean Data Processing and using Rob’s almost classic QVC approach, we developed some time ago also a set of Qlik script sub libraries to keep in the table tags a history of the incremental or partitioned that allows us to manually audit QVD’s when some of our clients with lots of data and some times instable data sets are calling us to troubleshoot their QVDs. It’s also an interesting architecture we had to develop… a Qlik Sense on Windows hosted in Azure , is extracting from an old AX hosted also in Azure and saving QVD’s (simple/incremental/partitioned) over a VPN to an on prem Qlikview Server & Publisher & Nprinting doing assembly & additional processing and serving final users.
The additional approach we had was to append in the table tags not only the starting and ending included Keys, but also summarizing info regarding the number of transactions included in the partition/increment and even sum(RelevantValue). This allowed us to understand faster when called to solve, if some inbetween incremental loads are missing in the data sets and a full reload is mandatory to recreate the correct data sets.
Thank you both one more time for sharing so much value around !
Thank you for your comments Cotiso, and glad to hear the tools I developed as part of the LDP framework helped you and you were able to add more functionality on top.
Cheers
Miguel
One month later, just run again into the post and , re-reading my comments, I’ve realized it can go even beyond: have the tags read again by an supervising script and send alerts when partitions or increments are missing, transforming the approach from a responsive one, to a proactive one…
Always inspiring, both !