Kategorien
Allgemeines Makros and Automatisation Qlik Tips n Tricks

QlikView Dynamic TableBox Creation

Reason

Sometimes it makes sense to pass some SQL Views through the whole ETL-Process to e.g. use the View “AS-IS” within a NPrinting Report. To achieve this behaviour, wenn need 2 dynamic steps:

Kategorien
Allgemeines Makros and Automatisation Qlik Tips n Tricks

Export a Chart or Tablebox via VBScript

As sometimes it makes sense to export data with a loop using VBScript. This little toolset should do the job for you:

Sub ExecByFieldValues

	SET Doc = ActiveDocument
	SET Field = Doc.Fields(getVariable("pstrFieldName"))
	SET Values = Field.GetPossibleValues
	
	FOR i = 0 TO Values.Count-1		
		ExportObject Values.item(i).Text
	NEXT

End Sub

Be aware that you need some helpers:

rem ** will read and return the content of a variable defined in script or document **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel

function getVariable(varName)
 Set v = ActiveDocument.Variables(varName)
 IF v Is Nothing THEN
     getVariable = ""
 ELSE
     getVariable = v.GetContent.String
 END IF
end function

rem ** will check if the folder exists, if not, will build it **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel
function MyCheckFolder (pdfPath)
	Dim objFSO
	Dim objFolder
	Dim Overwrite

	Set objFSO = CreateObject("Scripting.FileSystemObject")
	Overwrite = objFSO.FolderExists(pdfPath)

	Select Case Overwrite
	Case False: Set objFolder = objFSO.CreateFolder(pdfPath)
	Case Else
	End Select

	Set objFSO = Nothing: Set objFolder = Nothing
end function

and finally you can export the object like this:

Function ExportObject (pstrFieldFilter)
	
	SET Doc = ActiveDocument
	SET Field = Doc.Fields(getVariable("pstrFieldName"))

	Dim vstrExportPath
        Dim vstrExportFileName
        Dim vstrCompleteFile
    	
        'Lets create a temporary bookmark, to store the previous selection
	Doc.CreateUserBookmark "MakroStorage"
	
	'Select the Current fieldvalue
	Field.Select pstrFieldFilter
	
	SET obj = Doc.GetSheetObject(getVariable("pstrExpObject"))
	vstrExportPath = getVariable("pstrExpPath") & "\"
	'will check if the folder exists already or should be created if not so
	'MyCheckFlolder vstrExportPath
	
	vstrExportFileName = pstrFieldFilter & ".xls"
	vstrCompleteFile = vstrExportPath & vstrExportFileName
	'will Delete an old existing version
	deleteReport vstrCompleteFile
	obj.ExportEx  vstrCompleteFile , 5
	
	'Wait until object is created then release the objects
        Doc.GetApplication.Sleep 5000
	
	'reset the start selections
	Doc.RecallUserBookmark "MakroStorage"
	set obj = Nothing
	
	Doc.RemoveUserBookmark "MakroStorage"       

End Function

Take care that you’ve added the variables for the path in you enviroment variables or in your load script:

//Variables for the ExportObjectMakro
Let pstrFieldName = "Standortname";
Let pstrExpObject = "CH526";
Let pstrExpPath   = "C:\ttemp";

 

Kategorien
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
                    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"

 

Kategorien
Makros and Automatisation Qlik Tips n Tricks

QlikView User Access and Object Access

Introduction

Security is one of the main topics and issues, deploying data to different users or group of users. Additional you have to seperate between data filters and object/feature visibilities. To achieve a individual “restriction” we split access rules into:

  • ObjectRights (What a user can “see”)
  • AccessRights (Which data a user can “browse”)

 Scripts

Here you’ll find all scripts that are needed to implement the functionality.

Macros

Here you’ll find all VBA-Macros that are needed to use the functionality.

Loop Sheets

SUB LoopSheets

 REM ** loop through all sheets **
 REM @Author <Philipp@Frenzel.Net>
 REM beeye.org
 FOR i = 0 TO ActiveDocument.NoOfSheets - 1
     SET ss= ActiveDocument.GetSheet(i)
     varCompare = ss.GetProperties.Name 
     IF LEFT(varCompare,7) <> "Sandbox" THEN
      ReadObjectsAndSetRights varCompare
     END IF
 NEXT

 END SUB

Set Object Visibility

SUB ReadObjectsAndSetRights(SheetName)
 
 REM ** minimize all sheet objects on sheet Main **
 REM Rights by beeye.org
 REM Author Philipp Frenzel
 
 varVisibilityPrefix = "=if(only({<RightRule={'$(=OSUser())"
 varVisibilitySuffix = "'}>} RightAccess)=1,1,0)"
 
 SET s=ActiveDocument.Sheets(SheetName)
 FOR i=0 TO s.NoOfSheetObjects-1
     'the formula for our rights management
     varVisibility = varVisibilityPrefix & s.GetProperties.SheetId & s.SheetObjects(i).getObjectId & varVisibilitySuffix
     SET obj = ActiveDocument.GetSheetObject(s.SheetObjects(i).getObjectId)
     ' type 4 means Tablebox so there you can find the properties over the following context
     IF obj.getObjectType < 5 THEN
 SET objProp = obj.GetProperties
 SET objExpr = objProp.Layout.Frame.Show
 ' toggle invisible property of chart expression
 ' based on value of DEPT field selection
 objExpr.Expression.v = varVisibility
 'objExpr.Expression.v = 1 'uncomment this, if all should be visible
 objExpr.Always = false
 obj.SetProperties objProp
 END IF
 ' type 10 are Pivottables here we set the visibility under the layout tab
 IF (obj.getObjectType > 9 AND obj.getObjectType < 16) OR (obj.getObjectType > 19 AND obj.getObjectType < 24) THEN
 SET objProp = obj.GetProperties
 SET objExpr = objProp.GraphLayout.Frame.Show
 ' toggle invisible property of chart expression
 ' based on value of DEPT field selection
 objExpr.Expression.v = varVisibility
 objExpr.Always = false
 obj.SetProperties objProp
 END IF
 NEXT
 
 END SUB

 

Kategorien
Makros and Automatisation Qlik QlikView Workarounds Tips n Tricks

QlikView Export ChartTo XLS

This script enables you to export a ChartObject to xls by looping over a field, defined within a variable:

 //Variables for the ExportObjectMakro
Let pstrFieldName = "Standortname";
Let pstrExpObject = "CH526";
Let pstrExpPath   = "C:\ttemp";

REM ** MAKRO TO EXPORT OBJECT TO EXCEL **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel
Sub ExecByFieldValues

    SET Doc = ActiveDocument
    SET Field = Doc.Fields(getVariable("pstrFieldName"))
    SET Values = Field.GetPossibleValues

    FOR i = 0 TO Values.Count-1        
        ExportObject Values.item(i).Text
    NEXT

End Sub

Function ExportObject (pstrFieldFilter)

    SET Doc = ActiveDocument
    SET Field = Doc.Fields(getVariable("pstrFieldName"))

    Dim vstrExportPath
    Dim vstrExportFileName
    Dim vstrCompleteFile

    'Lets create a temporary bookmark, to store the previous selection
    Doc.CreateUserBookmark "MakroStorage"

    'Select the Current fieldvalue
    Field.Select pstrFieldFilter

    SET obj = Doc.GetSheetObject(getVariable("pstrExpObject"))
    vstrExportPath = getVariable("pstrExpPath") & "\"
    'will check if the folder exists already or should be created if not so
    'MyCheckFlolder vstrExportPath

    vstrExportFileName = pstrFieldFilter & ".xls"
    vstrCompleteFile = vstrExportPath & vstrExportFileName
    'will Delete an old existing version
    deleteReport vstrCompleteFile
    obj.ExportEx  vstrCompleteFile , 5

    'Wait until object is created then release the objects
    Doc.GetApplication.Sleep 5000

    'reset the start selections
    Doc.RecallUserBookmark "MakroStorage"
    set obj = Nothing

    Doc.RemoveUserBookmark "MakroStorage"       

End Function

rem ** will read and return the content of a variable defined in script or document **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel

function getVariable(varName)
 Set v = ActiveDocument.Variables(varName)
 IF v Is Nothing THEN
     getVariable = ""
 ELSE
     getVariable = v.GetContent.String
 END IF
end function

rem ** will check if the folder exists, if not, will build it **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel
function MyCheckFolder (pdfPath)
    Dim objFSO
    Dim objFolder
    Dim Overwrite

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Overwrite = objFSO.FolderExists(pdfPath)

    Select Case Overwrite
    Case False: Set objFolder = objFSO.CreateFolder(pdfPath)
    Case Else
    End Select

    Set objFSO = Nothing: Set objFolder = Nothing
end function

rem ** Function to delete existing documents, before printing/export the new one **
rem Developed by Frenzel GmbH
rem Author Philipp Frenzel
function deleteReport(rFile)
    set oFile = createObject("Scripting.FileSystemObject")

    currentStatus = oFile.FileExists(rFile)

    if currentStatus = true then
        oFile.DeleteFile(rFile)
    end if
    set oFile = Nothing
end function
''END MAKRO TO EXPORT OBJECT TO EXCEL ------------------------------
Kategorien
Allgemeines Makros and Automatisation Qlik QlikView Workarounds Tips n Tricks

Read and Write QlikView ObjectIDs by Macro

Here is a short script on how to read and write Object Id’s:

SUB ReadObjectsAndSetId(SheetName)

    rem ** minimize all sheet objects on sheet Main **
    rem Author Philipp Frenzel

    set s=ActiveDocument.Sheets(SheetName)
    for i=0 to s.NoOfSheetObjects-1
        'the formula for our rights management
        varVisibility = s.GetProperties.SheetId & s.SheetObjects(i).getObjectId

        Set obj = ActiveDocument.GetSheetObject(s.SheetObjects(i).getObjectId)

        Set objProp = obj.GetProperties        
        Set objExpr = objProp.Layout.Frame.Title

        ' toggle invisible property of chart expression
        ' based on value of DEPT field selection
        objExpr.Expression.v = varVisibility
        obj.SetProperties objProp
    next

END SUB

SUB LoopSheets

    rem ** loop through all sheets **
    rem Rights by Frenzel GmbH
    rem Author Philipp Frenzel
    for i = 0 to ActiveDocument.NoOfSheets - 1
        set ss= ActiveDocument.GetSheet(i)
        varCompare = ss.GetProperties.Name     
        IF LEFT(varCompare,7) <> "Sandbox" THEN
             ReadObjectsAndSetId varCompare
        END IF
    next

END SUB
Kategorien
Makros and Automatisation Qlik Tips n Tricks

QlikView Makro PDF Print with BullZip PDFCreator QlikViewPDF

Getting the Printer

First of all you need to download and install the printer from the official BullZip Webpage, which can be found here:

http://www.bullzip.com

After the download and installation (GhostScript Requiered) you’ll find a new Printer in your System Settings.

QlikView Parts

Preparing your System

Go to the macro modul in your QlikView Client. And write the following modules:

Sub PrintReports
  'This macro can be used to print pdf reports into the directory structure using bullzip pdf

  ActiveDocument.GetApplication.Sleep 2000
   ActiveDocument.PrintReport "RP02", "Bullzip PDF Printer"
  ActiveDocument.GetApplication.Sleep 10000

 MyPrintPDFWithBullZip "C:\Temp\18T.pdf"
End Sub

FUNCTION MyPrintPDFWithQlikViewPDF (pdfOutputFile)

    Set WSHShell = CreateObject("WScript.Shell")
    WSHShell.RegWrite "HKCU\Software\QlikViewPDF\OutputFile", pdfOutputFile, "REG_SZ"
    WSHShell.RegWrite "HKCU\Software\QlikViewPDF\BypassSaveAs", "1", "REG_SZ"

END FUNCTION

FUNCTION MyPrintPDFWithBullZip (pdfOutputFile)

 'need to init the printername, otherwise the assignment of the values will fail
 Set obj_printer_util = CreateObject("Bullzip.PDFUtil")
 printername = obj_printer_util.defaultprintername

 'init the BullZip preferences object
 set obj = CreateObject("Bullzip.PDFSettings") 'For 32Bit System u use PDFPrinterSettings

 obj.printername = obj_printer_util.defaultprintername 'assign the printername
 obj.SetValue "Output" , pdfOutputFile
 obj.SetValue "ConfirmOverwrite", "no"
 obj.SetValue "ShowSaveAS", "never"
 obj.SetValue "ShowSettings", "never"
 obj.SetValue "ShowPDF", "no"
 obj.SetValue "RememberLastFileName", "no"
 obj.SetValue "RememberLastFolderName", "no"
 obj.SetValue "ShowProgressFinished", "no"
 obj.SetValue "ShowProgress", "no"
 obj.WriteSettings True

END FUNCTION