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
Allgemeines Qlik

Global Calendar

Having a well structured calender within your qlikview app allows you to build faster charts and reports. Speak to your internal customers about their report layouts and you’ll see that most people have a common sense on how to combine data over time.

Below you will find a list of fields which we recommend to add it to your default calendar.

Definition:

FieldnameFormulaDescription
Calendar.Datedate(Field)Date in the system format, e.g. 01.01.2016 (DD.MM.YYYY)
Calendar.Yearyear(Field)Date in the format 2016 (YYYY)
Calendar.LYearyear(Field-365)Previous Year in the format 2015 (YYYY)
Calendar.LLYearyear(Field-730)PrePrevious Year in the format 2014 (YYYY)
Calendar.YearTDif(Field1=in year to date full months only in the format (1=true, 0=false)
Calendar.YearCTDif(Field<=today(),1,0)1=in year to date in the format (1=true, 0=false)
Calendar.LYearTDif(Field1=in last year to date full months only in the format (1=true, 0=false)
Calendar.LYearCTDif(Field<=today()-365,1,0)1=in last year to date in the format (1=true, 0=false)
Calendar.LLYearTDif(Field1=in previous last year to date full months only in the format (1=true, 0=false)
Calendar.LLYearCTDif(Field<=today()-720,1,0)1=in previous last year to date in the format (1=true, 0=false)
Calendar.Qtrquartername(Field)Date in the Format 1.QTR 2016
Calendar.LQtrquartername(Field-365)Date in the Format 1.QTR 2015
Calendar.LLQtrquartername(Field-720)Date in the Format 1.QTR 2014
Calendar.QtrTDif(quarter(Field1=quarter to last full month
Calendar.LYQtrTDif(quarter(Field1=quarter to previous year last full month
Calendar.LLYQtrTDif(quarter(Field1=quarter to previous previous year last full month
Calendar.MonthLmonthname(Field)Date in the format May 2016 (ddd YYYY)

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
Allgemeines Google Analytics Qlik

Loading Google Analytics with QlikView

General

To keep reports compatible, the following dimensions are alway loaded as a minimum:

  • ga:browser
  • ga:deviceCategory
  • ga:pagePath
  • ga:date

Add Measures

Now as the dimensions are set, you can add up to 5 measures to be grouped by and displayed.

QlikView Load Script

Trace loading the values day by day;

FOR zz = 2012 TO 2015
 FOR ii=1 TO 12 //monat
  FOR dd=1 TO 31 //tag

// Date period
LET jj = num($(ii),'00');
LET ee = num($(dd),'00');
LET ff = num($(dd),'00');

SET gDateStart='$(zz)-$(jj)-$(ee)';
SET gDateEnd='$(zz)-$(jj)-$(ff)';

TRACE ab hier beginnt die Schleife;

GoogleAnalyticsConnectorV3_DataFromQueryURI:
LOAD 
	'DE' AS Land,
	*;
LOAD
	dim_browser AS Browser,
	dim_deviceCategory AS Device,
	dim_pagePath AS Path,
	dim_date,
	makedate(mid(dim_date, 1, 4), mid(dim_date, 5, 2), mid(dim_date, 7, 2)) as date_qv AS ReportingDate,
	metric_users,
	metric_sessions,
	metric_pageviews,
	metric_exits,
	metric_exitRate
FROM
[http://localhost:5555/QVSource/GoogleAnalyticsConnectorV3/?table=DataFromQueryURI&appID=&feed=https%3a%2f%2fwww.googleapis.com%2fanalytics%2fv3%2fdata%2fga%3fids%3dga%2
%26start-date%3d$(gDateStart)
%26end-date%3d$(gDateEnd)
%26metrics%3dga%253Ausers%252Cga%253Asessions%252Cga%253Apageviews%252Cga%253Aexits%252Cga%253AexitRate%26dimensions%3dga%253Abrowser%252Cga%253AdeviceCategory%252Cga%253ApagePath%252Cga%253Adate]
(qvx);

TRACE ende der schleife;

STORE GoogleAnalyticsConnectorV3_DataFromQueryURI INTO '$(PATH_DATASTAGING)\GOOGLEANALYTICS\01DUMPFORMAT_USER_SESSION_PAGEVIEW_EXITS_EXITRATE$(zz)_$(jj)_$(ee).qvd' (qvd);

DROP TABLES GoogleAnalyticsConnectorV3_DataFromQueryURI;

  NEXT; //next day
 NEXT; //next monat
NEXT; //next jahr

If you don’t have QVSource running, you can use the query explorer and the url with the (1hour) token included to load the data like this. Then I recommend that you put this token into a variable to which will be passed to the script while you wanna load it.

Enhancement

If you have multiple sites you wanna load, just pack the hole load script into a sub and pass over at least the parameter:

  • gAppId

Then use this variable in your load url.

At least you can use an inline table to define your “websites” and then loop through all you sites:

GoogleId:
LOAD * INLINE [
    AppId, Land
    123, mydomain.CH
    456, mydomain.DE
    789, mydomain.AT
];

FOR I = 0 TO 2
	LET vGAppId = peek('AppId',I,'GoogleId');
	LET vCountryId = peek('Land',I,'GoogleId');

	CALL LoadGoogleId(vGAppId, vCountryId);
NEXT;

 

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
Allgemeines Developer Schulung Qlik

Project Documentation with github and markdown

A very important step while implementing more or less complex solutions is the availability of a documentation. While I’m really a fan of version based work, I will describe a quick an simple way, how I document my projects with github and markdown.

For all of you who don’t know markdown, it’s a simple “what you mean is what you get” syntax which can be compared to LATEX. (If you don’t know this eather, pls. give a search engine a visit) 😉

So what you acutally need is:

  • GitHub Account
  • TextEditor (min.) if you are the more “what you see is what you get” person, you’ll find a lot of editors in the web, even online based