QlikView Script Sub for Dataloading

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

Continue Reading

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

Continue Reading

Lesson 11 – complex data presentation

Overview In this lesson we get to know more complex data diagrams, which are properly suited for “intergroup” analysis. Trellis Trellis graphs serve the same visualization with a uniform scale to visualize different categories. You can decide yourself which dimensions you want to “distinguish”. In the advanced settings you can define the quantity per column and line.  Tables with conditioanl formatting Keep in mind, these visualizations ONLY work with the graph type “Table”! This form of visualization has proven it’s worth “exclusively” for power-useers. Otherwise the information density would be too high to keep track.  Sparklines Also ONLY possible with graph type “table”. Exception: you can also integrate these so… Read More

Continue Reading

Lesson 10 – Standard data presentation

Overview In this lesson we will get to know some of the most common charts and how we can make them prettier. Bar graph With the help of bar graphs comparisons between individual groups of values ​​can be created quickly (quantitatively). In doing so, the orientation of the charts can create a clean representation even in long legends. Combined bar/line graph This form of visualizations is properly suited for visualizing moving data with different units. It is important to use the second axis to, for example, prevent the vanishing of the percentage display. The “position” of the formula moves to the “right” side. for more complex visualizations it is practicable… Read More

Continue Reading

Lesson 9 – Basic pivot functions

 Overview In this lesson we are going to learn about the most important aggregation functions in detail and how to “modulate” them to our purpose. A short introduction into the set-analysis is going to show us one of the most powerful functionalities. Pivoting made easy To create a cross table right-click an “empty” area on the work surface and choose: Chart! Then the “Wizard” starts which allows you to create a cross table. After choosing the type, click on next. Select here, after which dimension/category the calculation of the values should happen. Specify the the wanted function/formula/calculation – here as a simple Count() – Quantity. You can install multiple formulas… Read More

Continue Reading

Lesson 8 – Working on the User-Interface

Overview Up until now we were almost exclusively occupied with the loading of data. Starting with this lesson, we are mainly focusing on presenting and analyzing the data. General settings Activating the “Design-Toolbar” is one of the most important presettings for a QlikView designer. You can manage the visibility in the menu option “View”. Overview of the control objects ListBox The listbox helps finding, selecting and analyzing data within a certain area. The following colour code for visualization has been established by QlikTech: The background is white without selection. Once you choose a selection it turns grün, the remaining options stay white and non-choosable values turn gray. By using the… Read More

Continue Reading

Lesson 7 – Exercises

Overview In this lesson you are going to repeat all you learned on your own with your acquired skills and techniques. Task 1 – Dataloader Adventure Works Create a dataloader with a connetion to the acces file – AdventureWorksSample which can be found in “Admin/Common/Libs/QVComponents/Examples/DbExtract” Qualify the datas and save them into the DataStaging (work with variables)   Include the data source as an ODBC system source. If you are using a 64Bit system it is important to activate the “Force32Bit” option when working with 32Bit data sources. (MS Access is 32 Bit) Your script for this first task should look something like this:

If you execute the script… Read More

Continue Reading

Lesson 6 – Script Tips ‘n’ Tricks

Overview This short lesson concentrates on typical questions from practice and their possible solutions. Marking of duplicates In practice it often happens that “duplicates” for one information exist but you only want to consider the “newest” dataset. To prevent this problem, QlikView offers so called “interrecord-functions” within scripts. These allow to access previous or following datasets during the loadtime of a source. The solution for our problem would look like this:

Dissolving of “multiple” keys In practice it often happens that a relation between 2 tables exists on multiple fields. To improve performance and to keep the datamodel “clean”, it is worth it to combine those keys with a… Read More

Continue Reading

Lesson 5 – Project organisation

Overview This lesson handles the general organisation and structuring of QlikView projects. Based on our longtime experience with software development and QlikView implementations, we here show our “Best Practice”. As easement for our own projects we provided an online template which enables the development of stable BI-solutions following our standards. https://github.com/FrenzelGmbH/qlik Folder structure ADMIN COMMON 00 CONFIG LIBS DEV 01 Dataloader 02 Datastaging 03 Datalogic 04 Datamart PROD << as DEV >> ANALYSIS COMMON DEV PROD CONSOLE MAINTANANCE DEPLOYMENT DOCS VERSIONING The goal of these structures is to create a distinct line between productive and development systems – without an additional testserver. Only if all logics work with test structures,… Read More

Continue Reading

Lesson 4 – Structuring scripts and using functions

Overview To not lose track in longer scripts, you have the possibility to structure the script. QlikView offers you to classify via index cards. Additionally we use easy functions to enrich our datamodel. Generate an index sheet To simplify the finding of ladelogics in the script, structure the script by creating new tabs or moving existing tabs. Try to find short but concise names for register riders to make navigation easier. It is especially useful to generate an “exit” register rider, which holds the command “EXIT SCRIPT;”. This makes it possible for complex data structures to stop “earlier” with the loading of the data and to analyze isolated logs. Functions… Read More

Continue Reading