Makros and Automatisation Qlik Tips n Tricks

Source Control for QlikView with git

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
                    	MsgBox (File.Name)
                    	fso.CreateFolder(Replace(File ,".qvw","-prj"))
                    END IF
                End If
            ' Recurse to check for further subfolders
        	RecurseFolders folder.Path
        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
  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
                              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"


Schreibe einen Kommentar