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:
Sub CreateProjectFolders ' Call the RecurseFolders routine ' Takes one argument - in this case, the Path of the folder to be searched RecurseFolders "D:\qlikview\" ' echo the job is completed MsgBox "Completed!" End Sub Sub RecurseFolders(sPath) Dim fso ' create a global copy of the filesystem object Set fso = CreateObject("Scripting.FileSystemObject") With fso.GetFolder(sPath) if .SubFolders.Count > 0 Then For Each Folder In .SubFolders 'WScript.Echo folder.path 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 ELSE MsgBox (File.Name) fso.CreateFolder(Replace(File ,".qvw","-prj")) OpenClose(File) END IF End If Next ' Recurse to check for further subfolders RecurseFolders folder.Path Next End if End With ' clean up Set fso = Nothing End Sub Function OpenClose(qvwFullPath) Dim Qv Set Qv = CreateObject("QlikTech.QlikView") Set docObj = Qv.OpenDocEx (qvwFullPath,0,false) ' Open the document docObj.Save docObj.CloseDoc OpenClose = qvwFullPath Set Qv = Nothing End Function
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:
sub ScanFolder(Root) for each FileExtension in 'xls' for each FoundFile in filelist( Root & '\*.' & FileExtension & '') if Right('$(FoundFile)',3) = 'xls' THEN File: LOAD * FROM [$(FoundFile)] (biff, embedded labels); STORE File INTO [$(FoundFile).csv] (txt); DROP TABLE File; end IF next FoundFile next FileExtension for each SubDirectory in dirlist( Root & '\*' ) call ScanFolder(SubDirectory) next SubDirectory end sub 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"