Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Qlik Sense documentation and resources.
6-minute video: https://www.youtube.com/watch?v=FQsTiMB9aX4
Building Excel-styled tables with different formulae on each row has always been a pain point in Qlik.
We have all created Excel styled spreadsheet using Valuelist as a dimension and PICK MATCH in the expression in order to achieve the cell level control as it is in Excel.
However, this becomes hard to read and maintain when expressions in cells get extremely complex and also fidgety when you need to add new lines in an existing front-end Qlik table.
So I looked for an external way of maintaining these and am now sharing the framework and files to be able to easily create, maintain and change these Excel-style tables in Qlik.
Please watch the YouTube video for more ample information, explanation and instructions.
QVD file with 249 countries based on ISO 3166-1 standard for name and abbreviation can be found in GitHub https://github.com/tonikautto/world-countries-regions-qlik/tree/master/QVD
Fields included in the QVD are...
QVD are based in XLS content, but the original data source is https://www.iso.org/iso-3166-country-codes.html,
QVD generator can be found in GitHub https://github.com/tonikautto/world-countries-regions-qlik
Examples for QlikView with Qlik GeoAnalytics.
Binning
This app shows an example of how the Binning feature can be used to create rectangular or hexagonal bins over Sweden. It also shows a drill-down hierarchy with two levels of rectangular bins and a point level.
Chart Layer example
An example of how the Chart Layer can be used to plot data points on graphs (both bars and pies) using two dimensions. This example shows how the average house prices in Sweden have developed over time.
Polylines and Symbols example
An example showing how to use the Line Layer to create Polylines and the Bubble Layer to create Symbols from image URLs.
Line Layer example – Storms
Example of Line Layer using Storm data to visualize historical storms.
EU statistics example
Uses: Bubble Layer with latitude and longitude measures, bubble size and color by measure, Area Layer with location ID for country codes, area color by measure, transparency.
Drill down example
A drill down example with three different ways of using drill down data to visualize data. Sheet 1: Drill down based on State - County - Zip Sheet 2: Drill down based on Zip.
Heatmap example
Uses: Heatmap Layer, drill down with layers active in different levels, Bubble Layer with visibility restricted to resolution limits.
US flight delay example
This example shows how Line Layer, among others, can be used to show routes between different positions and include average departure/arrival delays in the appearance of the lines.
The examples requires v 12 or later of QlikView.
This sounds absurd, but how many times do we experience problems when recording QVD, whether due to the action of antivirus, an application sharing the given moment among the most diverse generic errors of storage systems?
Here is a nice solution for you to make this attempt X number of times waiting for a Y interval of time.
And of course, so that you can reuse the code in your application.
Well, let's Qlik:
Sub Store_QVD(TableName, Path)
// Example: Call Store_QVD('Dim_Mytable', 'lib://Files/QVD/');
// Initials parameters:de
Set ErrorMode = 0; // Force to 0. Qlik will simply ignore the failure and continue script execution at the next script statement.
Set Store_QVD_Attempt = 0; // Force to initial 0.
Set Store_QVD_Attempt_Nr = 10; // How many times do you wanna try again the STORE command...
Set Store_QVD_Attempt_Sleep = 20000; // How many miliseconds to wait before a new attemp.
Do while True()
// Main command to store:
Store [$(TableName)] into [$(Path)/$(TableName).qvd] (qvd);
// Now let's check if everything worked out
If $(ScriptErrorCount) = 0 then
// If everything is OK and QVD was stored, continue the load script...
Exit Do
Else
Let Store_QVD_Attempt = Store_QVD_Attempt + 1;
Trace '## ERRORS until now: $(Store_QVD_Attempt).';
End If // Start at: If $(ScriptErrorCount) = 0 then
// If we already get all the attempts:
If ($(Store_QVD_Attempt) = $(Store_QVD_Attempt_Nr)) then
// Show the final error:
Trace '## ERROR to save [$(TableName)] into [$(Path)/$(TableName).qvd] after $(Store_QVD_Attempt_Nr) attempts.';
// Qlik Sense will trigger an "Execution of script failed..." error message immediately on failure, without prompting the user for action beforehand.
Set ErrorMode = 2;
End If
// If we get an error, inform:
Trace '## ERROR to save [$(TableName)] into [$(Path)/$(TableName).qvd]. Attempt $(Store_QVD_Attempt) of $(Store_QVD_Attempt_Nr).';
Sleep $(Store_QVD_Attempt_Sleep); // Wait for X milisecondss befor try again.
Loop // Start at: Do while True()
// Houston, we have a problem...
Set ErrorMode = 2;
End Sub // Start at: Sub Store_QVD(TableName, Path)
Data Model Simulator v1.1
A tool developed for the purpose of testing different data modelling scenarios in Qlik Sense or QlikView
The tool comprises of:
* A Qlik Sense and a QlikView app that respectively allow for both simulation data generation as well as scenario testing of the generated data.
The code is commented with instructions on how to generate the data and subsequently how to run the different model scenarios.
* An optional PowerPoint slide pack for those of you who have heard me present the material and wish to have a reminder at hand of the topics and learning points covered.
The types of data models currently simulated by the app are:
1. A Multi-Fact model with a Link-Table / Bridge Table representing the associations between the facts
2. A Multi-Fact where the facts have been appended into a single mixed fact. Sometimes referred to as a "concatenated" or Mixed-Fact model
3. An Attribution model : Typically used for modelling Budget/Forecast data storing ratio attributions in a separate table with a many-to-many relationship with the Budget/Forecast fact.
4. A Multi-State or Life-Cycle model : A model where different states of a fact is represented, e.g. FX trade data confirmation flows (captured trade -> split trade -> adjusted trade ...)
To do list for future versions:
* Key-Value pair structured fact versus horizontally aligned (cross-table) structured fact
Any and all feedback including ideas for improvements is most welcome.
Enjoy!
Ps. If you are interested in getting a talk on Qlik data modelling arranged for your team / organisation, drop me an email or a note on LinkedIn. I'm a regular speaker at Qlik events and meetups and I enjoy networking, knowledge sharing and exchanging ideas. Ds.