extended dataloader

 

Continue Reading

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.

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!

 

Continue Reading

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.

Now we have to loop through our table:

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

Here is just a loop to address every single month within our… Read More

Continue Reading

QlikView Script Sub for Dataloading

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

Continue Reading

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:  http://www.beeye.org/wp-content/plugins/download-monitor/download.php?id=3  Sample

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

After this we create a bar chart with the YourFieldname as Dimension and 1 as Formula: To color it as we wanted to, we will edit the background formula for the formula as here: Which leads us… Read More

Continue Reading

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:

Be aware that you need some helpers:

and finally you can export the object like this:

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

 

Continue Reading

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.… Read More

Continue Reading

QlikView and Google Analytics – Getting Started

Hello, as I got the challenge to integrate Google Analytics into our company warehouse with QlikView I will write a series of hopefully more or less usefull articles on how to fetch the data needed. For accessing the Google Analytics API (v3) you have two possibilities: You are a technic geek and you know how to use REST-Services and OPENAUTH You use the ready to use and well tested tool QVSource – www.qvsource.com As we had some time pressure for the analysis and I didn’t wanna write the „fetcher“ by myself. We decided to order and use the QVSource solution. Where to start? Facing the „KPI-Definition List“ from our marketing… Read More

Continue Reading

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… Read More

Continue Reading

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

Set Object Visibility

 

Continue Reading