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 simple trick.

Classification of data

ApplyMap function

Assign information based on a “map”. The following requirements on the map apply:

  • The assignment table exists of 2 columns, the first one holds the comparison value, the second one the future “class value”.
  • Both columns have to be named, although the name is not relevant later on. If you forget to name e.g. an inline-table, then the first value will never generate a hit.
  • When using the assignment table, the values of a certain field will be compared and if it leads to a hit, the assignment value will be “replaced” otherwise the original value persists.

If you are asking yourself why we don’t drag the data via Join into the datamodel, then the answer is point 3 of the just now mentioned points. A JOIN would always enlarge the table and make it impossible to “keep” the original value.

Example with the use of an inline-table:

IntervallMatch Function

Classification of values based on a lower and upper value. This function however can also be used for more complex questions like a time course analysis. To understand the principle behind the function, it is certainly easier to just use the example of a real estate agency, which wants to make a classification based on size and living space.

Can also be used for events and leads to the following datamodel in the background:

Intervalmatch

Performance – Linking of moving tables

The linking of moving tables – also with different column amounts and fields – gives, as long as all key fields are “overlapped”, a big performance improvement. As long as all key fields are included it is harmless for the later drilling in detail. Should however not all key fields be overlapping it is still possible, but the drilling can lead to breaks in detail, for which one might have to “explain” themselves.

Attention: QlikView atutomatically merges tables with the same structure. Which can lead to big problems, but also creates great opportunities. I will show you how to control this behaviour with the following example

In the last case you should keep working and change the table because there is a high risk of your datamodel to “burst”.

Execution of a function for every line of a table

QlikView permits you to create certain functions on your own and to request them with parameters. The easiest example is the writing of a message into a Log-file. (Just concerning the technique, don’t question the use!)

 

Leave a Reply