What you need?
So, facing the general question of “reasons” for lacking data quality, one of the most important thinks is transparency within the creation of the KPI’s. To reach this aim, I’ll switched to a full version control management with git to show my QlikView “customers” from where possible “issues” may resulting.
To reach this, I first registered myself at bitbucket – because the offer unlimited private repos for teams till 5 people. What means a perfect match for me! I although have an paid account on github, but for this “project” bitbucket fits my needs more.
Bitbucket offers a git client “sourcetree” that I although installed (free) and it works like charm!
Building the source to control
As we wanna track source changes, we first need to create _prj folders for all applications that we wanna manage by git. If you don’t know this folder, pls. just goolge it and you’ll find wonderfull explenations by other qlik’s that will help you to understand the contents. As I’m lazy and my QlikView Projects have more than 100’s of qvw’s. I wrote/adapted this little script to generate the _prj folders automatially:
' Call the RecurseFolders routine
' Takes one argument - in this case, the Path of the folder to be searched
' echo the job is completed
' create a global copy of the filesystem object
Set fso = CreateObject("Scripting.FileSystemObject")
if .SubFolders.Count > 0 Then
For Each Folder In .SubFolders
Set Files = Folder.Files
For Each File in Files
If fso.GetExtensionName(File.Name) = "qvw" Then
IF fso.FolderExists(Replace(File ,".qvw","-prj")) THEN
' NOTHING NEEDS TO BE DONE
' Recurse to check for further subfolders
' clean up
Set fso = Nothing
Set Qv = CreateObject("QlikTech.QlikView")
Set docObj = Qv.OpenDocEx (qvwFullPath,0,false) ' Open the document
OpenClose = qvwFullPath
Set Qv = Nothing
After this, we’ll have a wonderfull set of xml’s and txt/dat within the _prj folder. And suprise, they can be tracked within source control!
Convert XLS to TXT in QlikView
As some of you might store mappings inside XLS-Files, I coded a small load script, that will convert your xls-files to txt, so changes within your mappings or intervall maps can be tracked to! Here is the code:
for each FileExtension in 'xls'
for each FoundFile in filelist( Root & '\*.' & FileExtension & '')
if Right('$(FoundFile)',3) = 'xls' THEN
FROM [$(FoundFile)] (biff, embedded labels);
STORE File INTO [$(FoundFile).csv] (txt);
DROP TABLE File;
for each SubDirectory in dirlist( Root & '\*' )
Call ScanFolder('D:\qlikview\ADMIN\00 Config') ;
Add a local “mirror”
After working in praxis, I introduced a local mirror to my qlikview work directory, that only contains the txt, xml, csv, xls files. This is much better, as all qvw and qvd files are not really needed within the source control!
To achieve this, I just wrote a small batch file to copy the relevant folders. The /XD-Parameter allows me to “skip” predefined folders:
robocopy "D:\qlikview" "D:\qvsources" *.xml *.txt *.xls* /e /copy:DT /XD "D:\qlikview\pfrenzel"