HR – Abwesenheitsverwaltung beeye.hr

Urlaubs- und Krankenstandsverwaltung

Verwalten auch Sie in Zukunft mir unserer beeye.hr Lösung die Mitarbeiterabwesenheiten für Ihr Unternehmen. Im letzten Release 1.1 unterstützen wir die folgenden Basisfunktionalitäten:

Arbeitszeitplanverwaltung
Berechnung von
- Urlaubsneuansprüchen
- Urlaubsaliquotierungen
- Urlaubsrückstellungen
Anwesenheitsverwaltung
Sonderabwesenheiten
- Hochzeit
- Krankenstand
- Todesfall
- Umzug
Urlaubsantrag Genehmigungsprozess
- Antragsteller
- Prüfung und Freigabe
- Korrektur
- Buchung
Mitarbeiter Dashboard
Hinweissystem
- Zeitüberschreitungen
- Krankenstandsmeldung
- Vertretungsprobleme

2016/04/062017/05/22

myStorage – SelfStorage Business Intelligence Platform on Caravel

A couple of days ago, airbnb – one of the shared economy flag ships – released it’s own BI platform to the open source community. Comparable to solutions like Kibana from Elastic – it’s much more BI than LOG driven. Those of you who know Logstash and Kibana know what I mean! 🙂 For our kind of business Caravel seems like a much better fit! It cares about security and you can build your own global KPI Dictionary to include the same values into several dashboards. Let’s get started!

Installation

Install Docker http://www.docker.com
Install the Docker Image from Caravel (pls. check http://hub.docker.com for latest version)

First steps

After you have started the docker container, pls. ensure that you have the python drivers installed for your database. We decided to run our mysql database from out storage software.

Setup your connection string

Here you can add a new connection and pass the mysql uri like this:

mysql://soft2:XXXXXXXXXX@192.168.99.100:3306/soft2

Add tables to run your queries

One of the problems you might face, that you need to know which tables are existing. If you aren’t sure about it, you can go back to the database connection uri and click the TEST Button and all tables in the database will be listed on the page.

2016/03/032016/03/03

Eye-Tracking

What Is Eye-Tracking

Eye tracking is the process of using sensors to locate features of the eyes and estimate where someone is looking (point of gaze). Our technology relies on infrared illumination and uses advanced mathematical models to determine the point of gaze. We’ve spent a tremendous amount of effort on making it fully automatic and easy to set up so that it works accurately and reliably in a wide range of environments.Eye tracking can be used in a wide variety of applications typically categorized as active or passive. Active applications involve device control, for example aiming in games, eye activated login or hands-free typing. Passive applications include performance analysis of design, layout and advertising. Other examples are vehicle safety, medical diagnostics and academic research.

Our Service

We optimize your user experience. Back up your design with quantifiable data. We visualize how our UX-Design works using heat maps.
Data captured includes:

Thinkaloud voice recording
Webcam recording
Mouse clicks
Pupil diameter data
Where users stop scrolling

2016/02/242016/04/08

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:

Fieldname	Formula	Description
Calendar.Date	date(Field)	Date in the system format, e.g. 01.01.2016 (DD.MM.YYYY)
Calendar.Year	year(Field)	Date in the format 2016 (YYYY)
Calendar.LYear	year(Field-365)	Previous Year in the format 2015 (YYYY)
Calendar.LLYear	year(Field-730)	PrePrevious Year in the format 2014 (YYYY)
Calendar.YearTD	if(Field	1=in year to date full months only in the format (1=true, 0=false)
Calendar.YearCTD	if(Field<=today(),1,0)	1=in year to date in the format (1=true, 0=false)
Calendar.LYearTD	if(Field	1=in last year to date full months only in the format (1=true, 0=false)
Calendar.LYearCTD	if(Field<=today()-365,1,0)	1=in last year to date in the format (1=true, 0=false)
Calendar.LLYearTD	if(Field	1=in previous last year to date full months only in the format (1=true, 0=false)
Calendar.LLYearCTD	if(Field<=today()-720,1,0)	1=in previous last year to date in the format (1=true, 0=false)
Calendar.Qtr	quartername(Field)	Date in the Format 1.QTR 2016
Calendar.LQtr	quartername(Field-365)	Date in the Format 1.QTR 2015
Calendar.LLQtr	quartername(Field-720)	Date in the Format 1.QTR 2014
Calendar.QtrTD	if(quarter(Field	1=quarter to last full month
Calendar.LYQtrTD	if(quarter(Field	1=quarter to previous year last full month
Calendar.LLYQtrTD	if(quarter(Field	1=quarter to previous previous year last full month
Calendar.MonthL	monthname(Field)	Date in the format May 2016 (ddd YYYY)

2016/02/042016/03/16

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

/**

* 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

2015/12/182016/04/07

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;

/**

* 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);

/**

* 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);

2015/12/092015/12/09

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    
];

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;

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;

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

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

2015/12/022015/12/02

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

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;

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;

2015/07/252015/09/14

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

/**

* 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!

2015/07/182015/07/18

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

No FACTS? Frenzel GmbH!

Posts

HR – Abwesenheitsverwaltung beeye.hr

Urlaubs- und Krankenstandsverwaltung

myStorage – SelfStorage Business Intelligence Platform on Caravel

Installation

First steps

Setup your connection string

Add tables to run your queries

Eye-Tracking

What Is Eye-Tracking

Our Service

Global Calendar

extended dataloader

QlikView Generating Year over Year Targets

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

Loading Google Analytics with QlikView

General

Add Measures

QlikView Load Script

Enhancement

QlikView Script Sub for Dataloading

Project Documentation with github and markdown