Tuesday, September 1, 2015

Sorting Textual Dates in Qlikview Chart

I have a chart with a calculated date dimension.  One of the issues I had in the past with original versions PowerPivot was that there was only 1 possible sort order based on display.

So what happens when Apr comes before Jan in a textual sort in Qlikview?

This calculated dimension expression takes advantage of the dual function to represent dates as numeric for sorting purposes:

=dual(month(MaturityDate) & '-' & Year(MaturityDate),Num(year(MaturityDate),'0000') & num(Month(MaturityDate),'00'))

By sorting numeric, this expression will show the proper sort order for dates. 

Helping threads here.
https://community.qlik.com/blogs/qlikviewdesignblog/2014/07/18/dual-exists-useful-functions
https://community.qlik.com/thread/58791

Saturday, April 18, 2015

Avoiding Associating with Crappy Data

hallĂ„.  

Welcome to my blog.  This blog will cover some of the interesting tricks and features I find when using Qlik tools.

A Brief History of Qlikview, from 1994 to present, and in Swedish.

Loading Data to Qlik

Qlikview and Qliksense use load scripts to generate the associative model.  There are a million and 7 ways to load data to Qlik tools.

More here.

Table Joins & Qualify

Why are you joining data?  Don't join data. Lookup data...

If you have to join data...

Joins and Lookups in Qlik scripts

In a Qlikview Load Script, QUALIFY adds 2-part naming to your data model table or fields within the table.  Qlikview, by default, associates fields with the same names.  QUALIFY ensures that the associations between other tables doesn't happen, unless you want them to.  Fields coming into your model will be prefixed by the name of the table.

This keyword might stop you from doing bad things with your Qlikview models.

eg. from David White in Qlikview Community

QUALIFY *;
UNQUALIFY OrderID;

Orders:    
     LOAD OrderID,
               OrderName,
               CustomerName,
     FROM xxx.xls;

UNQUALIFY *;

Unqualify

The QUALIFY statement can act on all tables in the model unless UNQUALIFY keyword is used.

Synthetic Tables

Synthetic tables are created when more than 1 pair of identical data fields span two or more tables.  These are "$syn" tables and keys.

See Qlikview Scripting

and Synthetic Keys in Qlikview - simplified!

When dealing with multiple tables using a loop or wildcard load, you may need to perform a bit of additional work to create a single resident table after the load instead of multiple tables.


Synthetic keys are sometimes frowned upon due to other issues with the model.  When they show up, sometimes memory is increased and performance is decreased.  Removing them sometimes helps.  It was probably your data model and not the generated key.

Correlation vs. causation



Mapping Load (left join?)

Generally, it is almost always better to use mapping load with APPLYMAP rather than joins.  Load a mapping table with the MAPPING keyword.  Load a fact table and apply the lookup fields to the fact table load script by using the ApplyMap keyword.


Also see Juan Vitantonio's post, Normalizing your data model.

QVD

A Qlikview Data file is one of the more interesting features of Qlikview.  After a table is loaded into the Qlikview memory, it can be stored into a QVD file.  This is a copy of the RAM as it was loaded by Qlikview.  It is saved in a highly compressed format.  

In one example, I took a table from SQL 2008 R2, a 700MB (data, not including the numerous indexes) 132 column denormalized table with lots of strings and numeric fields, and took it down to a an 18MB QVD file.

The more useful examples of leveraging QVD files are when these files become larger and need to be reloaded into multiple applications.  Since the QVD is a copy of the RAM, large QVD files are nearly instantaneously loaded into the model when optimized.  

For reference data, historical data and optimizing the star model, QVD files appear invaluable.

Partial load of QVD

How to "zero-out" QVD files using the emptyQVD function

Some Useful Practices

  • QVW with just your charts and interface
    • QVW (Binary Loaded) with just tables as your model
    • 1 table per script tab
    • ApplyMap instead of associative joins
    • QVD(s) (Binary Loaded) with each table
    • QVD files for redundantly used data
    • QVD Generators (QVW files writing QVD files)
    • Cross table Expressions defined in script instead of UI
    • Explicitly defined keys to keep model clean
    • TRACE your scripts
    • AUDIT your scripts

Community Links

QVD Viewer

Audit Logging

Synthetic Keys


https://community.qlik.com/thread/132089
https://community.qlik.com/thread/29254
https://community.qlik.com/thread/125090
https://community.qlik.com/thread/54083

Welcome

HelloWorldTable:
Load * inline
[
What,Who
HELLO,WORLD
];