Kategorien
Allgemeines Qlik Script

Datamodelling Qlik Concatenate, Power BI Combine, SQL Union

The last 6 months have been quiet full of many new things to learn – after working more than 10 years with qlik and SQL a new player named “Power BI” has been added to my skills. So getting more and more used to it, I feel, like I should give other qlik developers to understand faster how both world solve the same issues;)

The issue is simple and I use it very often for the appending two fact tables with each other. I keep this solutions “small” so I’m not showing you how to create a “manual” demo table…

Solution in SQL

Select
 eventname
 ,timestamp
FROM table1
UNION ALL
Select
 eventname
 ,timestamp
FROM table2

So this looks quiet simple 😉

Solution in Qlikscript

Table1:
LOAD
 eventname
 ,timestamp
FROM table1.csv;

Concatenate(Table1)
LOAD
 eventname
 ,timestamp
FROM table2.csv;

Looks simple too!;)

Solution in PowerQuery

...
 table1 = Source,
 table2 = SourceTwo,
 ResultTable = Table.Combine({table1,table2}),
...

This seems to be the shortest code sample, anyway you should not forgett, that the datasources are different, so for example 1, this can only be run on a SQL-Server. Qlik Script includes the datasource already in the script (csv-files) and for powerquery I ignored to add the connection to the datasources, which would have blown up the sample!

Kategorien
Allgemeines QlikView Workarounds Tips n Tricks

Qlik XML aus Feld in Tabelle einlesen

Ausgangslage

Der Kunde liefert eine Wertetabelle, bei der in einer der Spalten XML-Daten ohne feste Struktur vorhanden sind. Diese sollen so aufbereitet werden, dass diese Struktur als eigene Felder im Datenmodel aufscheinen.

Tabelle:

idAttributeAttributevalueFromDate
11<Attr1>123</Attr1><name>Test</name>42005
22<price>10.50</price>41911

Vorgehen

  • Daten ergänzen <root> node
  • Tabelle in XML Textdatei exportieren .xml (txt)
  • Tabelle als XML wieder einlesen (Assistent)

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
Qlik Tips n Tricks

extended dataloader

/**
 * LoadSourceTableByName
 * @param TableName String the name of the table that will be exported
 * @param Historical Boolean if a historical version will be saved on a monthtly basis, pls. check comment!
 * @version 1.01
 * @author Philipp Frenzel <philipp@frenzel.net>
 */

SUB LoadSourceTableByName(NameSpace, TableAlias, TableName, Historical)

LET varTableName = TableName;
LET varTableAlias = TableAlias;
LET varNameSpace = NameSpace;

QUALIFY "*";
 
$(varTableAlias):
LOAD *;
SQL SELECT * FROM spaceman.$(varTableName);
 
UNQUALIFY "*";
 
CALL Qvc.Log('No of rows loaded in table: $(varTableAlias)' & NoOfRows('$(varTableAlias)'),['INFO']);
 
// Here we store the table as is into the filesystem 
STORE $(varTableAlias) INTO "$(PATH_DATASTAGING)$(varNameSpace)_$(varTableAlias).qvd" (qvd);

IF Historical = 1 THEN

// ATTENTION, if you save historical data, pls. ensure that a folder with the tablename name exists within the datastaging root folder!
STORE $(varTableAlias) INTO "$(PATH_DATASTAGING)HISTORY/$(varTableName)/$(varTableName)$(VERSIONDATE).qvd" (qvd);

END IF

DROP TABLE $(varTableAlias);

END SUB

 

Kategorien
Allgemeines Qlik Script Tips n Tricks

QlikView Generating Year over Year Targets

From time to time you have to create budget figures upon a percentage change compared to the last year. This can be a little bit difficult, depending on the complexity of the KPI. The following example is build upon a pre-cleaned datamart and shows how you create the monthly figures for you budget.

/**
* Find the opening balance for the sqm this year
*/
OpeningSQM:
NOCONCATENATE LOAD Distinct  
     %SiteID, 
     date(monthend(ReportingDate) + 1) AS ReportingDate //need to cast it in the next month to have the start in january
     ,'BUD'  AS MM_TYPE
     ,'2015' AS BUD_VERSION
     ,'SQM_CUM_OPENING'  AS BUD_KPI
     ,sum(KPI.UnitMoveIn_SQM_CUM) - sum(KPI.UnitMoveOut_SQM_CUM) AS Budget.Value
FROM
[..\04 Datamart\EVENTS_ALL_MONTHLY_KPI.qvd](qvd) 
WHERE 
     month(ReportingDate) <= 12 AND year(ReportingDate) = 2014
GROUP BY
     %SiteID, 
     ReportingDate;

/**
* Let's add the SQM changes by month, to make the cumulated calculation
*/
Concatenate(OpeningSQM)
LOAD
      *
Resident FinalBudget
WHERE
	BUD_KPI = 'SQM';
	

//Lets build the cumulated sqm
FOR MonthIter = 1 TO 12

	Concatenate(FinalBudget)
	LOAD
		%SiteID
		,MM_TYPE
		,BUD_VERSION
		,'SQM_CUM' 					AS BUD_KPI
		,floor(max(ReportingDate)) 			AS ReportingDate
		,sum(Budget.Value)				AS Budget.Value
	RESIDENT OpeningSQM
	WHERE 
		month(ReportingDate) <= $(MonthIter)
	GROUP BY
		%SiteID
		,MM_TYPE
		,BUD_VERSION
	;	
	
NEXT;

DROP TABLE OpeningSQM;

As you can see the real magic here is done in the FOR-TO-Loop, here it shows a cumulated budget figure, but you can easily change to a mothly figure!

/**
* Find the opening balance for the occ units this year
*/
LET InsuranceFactor = 1.05;

CONCATENATE (FinalBudget)
LOAD 
	%SiteID,
	floor(monthname(ReportingDate) + 365) AS ReportingDate
	,'BUD'  AS MM_TYPE
	,'2015' AS BUD_VERSION
	,Lead.CleanLeadSource AS BUD_KPI
	,Lead.CleanLeadSource
	,count(DISTINCT %ActionID) * $(InsuranceFactor) AS Budget.Value
FROM
[..\04 Datamart\INVOICES.qvd]
(qvd)
WHERE
	year(ReportingDate) = 2014 AND 
GROUP BY
	%SiteID
	,monthname(ReportingDate);

 

Kategorien
Allgemeines Qlik Script

Load multiple GoogleAnalytics-Properties at once w/ dynamic metrics in QlikView

Our objective is to load GoogleAnalytics data, seperated in months with a dynamic metric, fixed dimensions and for multiple Properties. To achieve this we’re using the QlikView addon: QVSource. The script contains the following (important) variables:

  • gAppId = googleAnalytics PropertyId
  • vMetric  = the dynamic metric, we want to load (note that this script will only load 1 metric per request)

First of all we need to define our googleAppIds within a static table.

GoogleId:
LOAD * INLINE [
    AppId, Land
    12345678, AT
    23456789, CH
    34567890, DE    
];

Now we have to loop through our table:

Let vStartTime = now();
Let vNr = NoOfRows('GoogleId')-1;

Trace $(vNr);

FOR i = 0 TO  $(vNr)
        Trace Loading $(i);

        LET vGAppId = peek('AppId',i,'GoogleId');
        LET vCountryId = peek('Land',i,'GoogleId');
        
        Trace start Sub loadGoogle $(vGAppId) and $(vCountryId);
        CALL loadGoogleId(vGAppId, vCountryId);
NEXT;

 

As our loadGoogleId Sub is still missing we have to add this here:

SUB loadGoogleId(gAppId, CountryCode)

        Trace loading the values day by day;
        Set x=1;        
        
        FOR zz = 2010 TO 2015
                FOR ii=1 TO 12 //monat
                                // Date period                        
                                LET jj = num($(ii),'00');
                                LET ee = num($(dd),'00');
                                LET ff = num($(dd),'00');
                                
                                LET vMonthend = left(monthend('01.' & $(jj) & '.' & $(zz)),2);
                                SET gDateStart='$(zz)-$(jj)-01';
                                SET gDateEnd='$(zz)-$(jj)-$(vMonthend)';
        
                                CALL LoadGoogle('users');
        
                                TRACE ende der schleife;
        
                                STORE GoogleAnalyticsConnectorV3_DataFromQueryURI INTO '$(PATH_DATASTAGING)\GOOGLEANALYTICS\$(CountryCode)_FILENAME_HERE_$(zz)_$(jj)_test.qvd' (qvd);
        
                                DROP TABLES GoogleAnalyticsConnectorV3_DataFromQueryURI;
                                x=x+1;
                        //NEXT; //next day
                NEXT; //next monat
        NEXT; //next jahr
END SUB;

Here is just a loop to address every single month within our prefered timespan. (one step for every month, as we want one table/file for every month). We call LoadGoogle in every step and store it afterwards.

LoadGoogle is the Sub, where the true magic happens. You have to build your LOADing-Statement with the QVSource-GoogleAnalyitcsConnector / DataFromQueryURI. Now you have to modify your string like the one below (just add the variables where they are required):

SUB LoadGoogle(vMetric)

GoogleAnalyticsConnectorV3_DataFromQueryURI:
LOAD
        dim_source,
        dim_adContent,
        dim_deviceCategory,
        dim_region,
        dim_hostname,
        dim_pagePath,
        dim_date,
        makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as date_qv,
        metric_users
FROM
[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV3/?table=DataFromQueryURI&appID=&feed=https%3a%2f%2fwww.googleapis.com%2fanalytics%2fv3%2fdata%2fga%3fids%3dga%253A$(gAppId)%26start-date%3d$(gDateStart)%26end-date%3d$(gDateEnd)%26metrics%3dga%253A$(vMetric)%26dimensions%3dga%253Asource%252Cga%253AadContent%252Cga%253AdeviceCategory%252Cga%253Aregion%252Cga%253Ahostname%252Cga%253ApagePath%252Cga%253Adate]
(qvx);

END SUB

 

Kategorien
Developer Schulung Qlik Script

QlikView Script Sub for Dataloading

/**
 * LoadSourceTableByName
 * @param TableName String the name of the table that will be exported
 * @param Historical Boolean if a historical version will be saved on a monthtly basis, pls. check comment!
 * @version 1.0
 * @author Philipp Frenzel <philipp@frenzel.net>
 */

SUB LoadSourceTableByName(TableName, Historical)

LET varTableName = TableName;

QUALIFY "*";
 
$(varTableName):
LOAD *;
SQL SELECT * FROM spaceman.$(varTableName);
 
UNQUALIFY "*";
 
 
// Here we store the table as is into the filesystem 
STORE $(varTableName) INTO "$(PATH_DATASTAGING)$(varTableName).qvd" (qvd);

IF Historical = 1 THEN

// ATTENTION, if you save historical data, pls. ensure that a folder with the tablename name exists within the datastaging root folder!
STORE $(varTableName) INTO "$(PATH_DATASTAGING)HISTORY/$(varTableName)/$(varTableName)$(VERSIONDATE).qvd" (qvd);

END IF

DROP TABLE $(varTableName);

END SUB

Sorry, typo in the path variable, now fixed! Enjoy the script!

Kategorien
Qlik QlikView GUI Tips n Tricks

QlikView – Color Blend by Dimension Value

Overview

Sometimes you wanna define a “color schema” by the values of a dimension. The easiest way is to generate a mapping table where each dimensionvalue gets an color assigned.

Downloads

First load the color table as CSV from here and save them into the same folder where you wanna create and run the QlikView Worksheet:

 Sample

//Load ColorTable as IS
// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net //
// ************************************************** //

SET varStringDocumentName = "=DocumentName()";

DimColorTable:
LOAD
 'col' & ColorName AS ColorName,
 RGB(Red,Green,Blue) AS ColorCode;
LOAD ColorName, 
 Red, 
 Green, 
 Blue
FROM
[Beeye QlikView Colortable.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);


// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net //
// ************************************************** //

//first init variables
// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net        //
// ************************************************** //

SET varStringDocumentName = "=DocumentName()";

ColorTable:
LOAD ColorName, 
     Red, 
     Green, 
     Blue
FROM
[Beeye QlikView Colortable.csv]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);


// ************************************************************** //
// Dynamic Variables by Frenzel GmbH - COPY 2013 www.frenzel.net  //
// AUTHOR Philipp Frenzel <philipp@frenzel.net                    //
// ************************************************************** //

//Get the number of available rows in the rights table
LET varIntegerNoVariables = NoOfRows('ColorTable');

TRACE Number of Dynamic Color Vars: $(varIntegerNoVariables);
 
//Lets build the dynamic variables
FOR DatenbankIter = 0 TO $(varIntegerNoVariables) - 1
 
 	//here we build the logic for the color coding
	LET curVariableName = peek('ColorName',DatenbankIter,'ColorTable');
	LET col$(curVariableName)= 'rgb(' & peek('Red',DatenbankIter,'ColorTable') &',' & peek('Green',DatenbankIter,'ColorTable') &','& peek('Blue',DatenbankIter,'ColorTable') &')';
	
NEXT;

DROP TABLE ColorTable;

// ************************************************** //
// AUTHOR Philipp Frenzel <philipp@frenzel.net        //
// ************************************************** //


//then we assign the values to a dimension mapping
//create the dimension mapping
Map_Color:
MAPPING
LOAD * INLINE [
 YourFieldname, Color
 a, colCadetBlue
 b, colCadetBlue1
 c, colCadetBlue2
 d, colCadetBlue3
 e, colCadetBlue4
];

//load you data into the application

Testdimension:
LOAD * INLINE [
 YourFieldname
 a
 b
 c
 d
 e
];

//assign the colors
Dimension:
NoConcatenate LOAD
 *
 ,applymap('Map_Color',YourFieldname,'colBlue') AS ColorName
RESIDENT Testdimension;

DROP TABLE Testdimension;

If you wanna update the colors “online” just replace the first load with:

DimColorTable:
LOAD
 'col' & ColorName AS ColorName,
 RGB(Red,Green,Blue) AS ColorCode;
LOAD ColorName, 
     Red, 
     Green, 
     Blue
FROM
[http://www.beeye.org/wp-content/plugins/download-monitor/download.php?id=3]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq, no eof);

After this we create a bar chart with the YourFieldname as Dimension and 1 as Formula:

Screenshot 2015-04-02 12.55.30

To color it as we wanted to, we will edit the background formula for the formula as here:

Screenshot 2015-04-02 14.26.18Which leads us to the following result:

Screenshot 2015-04-02 14.27.15

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
Google Analytics Qlik QlikView Workarounds Tips n Tricks

QlikView and Google Analytics – primary key issues

The main challenge I’m facing now, is the definition of a primary key, that allows me to build up my own data mirror to google analytics without messing up the metrics. Walking around on the internet, I found a lot of interesting articles about this and the best solution to come up with, seems to be the option to hand over custom variables to google analytics. This custom variables allow you to identify a single session and then you can grep the related data to it!

For the historical data, I have to come up with another solution, as the tracking ID will only be implemented for the upcoming periods. And as google doesn’t want people to be able to track the visitors down to the detail record (limit of 10 to dimensions and metrics) I’ll have to work with a “bridge” anyway.

Update

After some time of investigation, with the new google universal analytics you can use the following steps to get a unique primary key!

  1. You need to login to your google analytics account.
  2. Screenshot 2014-11-11 11.47.22 Then enter the “Admin” Menu, so you can add the new custom dimension.
    Screenshot 2014-11-11 11.47.31
  3. After you generated the custom variable, google offers you the code to include into your webpage:
    Screenshot 2014-11-11 11.48.08

So by doing this, the new custom dimension is available and can be “queried” although by the connector.