The best way to move QVD files quickly

During development, one often saves considerable reload time by having local copies of QVDs instead of using files on network or server locations. (Stored locally in an encrypted volume, of course.)

Your first inclination might be to simply copy and paste the QVDs to your machine, but zipping the QVDs first yields significant compression, which will save you considerable time with larger files. (NB: Depending on the environment, you may want to copy the QVDs, then zip the copies, to prevent locking the originals from being used or reloaded during the zipping process.)

Recently, I wanted to grab three QVDs from a server, totaling 1.55GB. In addition to zipping the files, I was also curious what loading all of them into a single QVW and using QUALIFY * would yield in file size.

QVD Size Reduction

To my surprise, zipping was even smaller than the QVW with compression set to High by about 8MB…thus saving myself from the immediate need to write an “unloading” application, which would binary load that QVW, alias to remove field name qualification, store to QVD, then drop tables from memory.

So next time you are moving files, save some time and bandwidth and zip them first.

Share

No Comments

Encryption In QlikView – Securing your data, for whatever reason…

I recently had the requirement to encrypt and decrypt data in a QlikView document using a key. This post details the problem, solution and advanced examples for securely storing sensitive information in QVDs.

 


 

The Problem — No Built-In Encryption

Encryption appears to be possible with Expressor, but I was unable to find any documented encryption functions for QlikView Desktop.

There was one discussion on the QlikView community where an individual was trying to implement encryption with a VB script macro, but certain values would not encrypt. I spent a little time trying to get that to work before giving up and taking a few days off from the problem.

 


 

The Solution

QlikView has two macro modes — JavaScript and VB Script. Using a self contained JavaScript encryption implementation and a few helper functions in the macro module, it is possible to encrypt and decrypt values in QlikView.

CyrptoJS fit the problem perfectly. Create an empty QVW and copy this into the macro module. Also, add the encrypt/decrypt helper functions below at the end of the macro module.

function encrypt(value, key) {
    return CryptoJS.AES.encrypt(value, key).toString();
}
function decrypt(value, key) {
    return CryptoJS.AES.decrypt(value, key).toString(CryptoJS.enc.Utf8);
}

Now QlikView can encrypt and decrypt values using a specified key.

// Setup the encryption key
Let vEncryptionKey = 'Your Encryption Key Goes Here!099';

// Example with variable
Let vName = 'Justin';
Let vEncryptedName = encrypt('$(vName)', '$(vEncryptionKey)');
Let vDecryptedName = decrypt('$(vEncryptedName)', '$(vEncryptionKey)');

// Example with LOAD
ExampleWithLOAD:
Load
    Name,
    EncryptedName,
    decrypt(EncryptedName, '$(vEncryptionKey)') as DecryptedName
;
Load
    Name,
    encrypt(Name, '$(vEncryptionKey)') as EncryptedName
;
Load
    'Justin' as Name
AutoGenerate 5;

Note the load example will produce 5 unique encrypted values for the same input. This will be problematic for maintaining compression when storing to QVD.


 

Advanced Examples

To understand the examples below, it is important to understand how QlikView stores data. At a high level, QlikView will keep a list of distinct values for every field in the data model as well as a pointer table that makes up the actual representation of the data.

If the encryption routine returns a unique value for every instance, QlikView will not be able to compress the data set as it normally would.

The code below demonstrates this behavior —

UnencryptedDataSourceWithDuplicates:
Load * Inline [
First Name, Last Name, SS#, DOB
John, Smith, 123-45-6789, 3/22/1944
John, Smith, 234-56-7890, 5/22/1990
Alex, Moore, 554-76-8859, 12/2/1974
Alex, Johnson, 577-62-9281, 3/22/1944
Stephanie, Moore, 112-24-1988, 8/21/2003
];

Store UnencryptedDataSourceWithDuplicates Into UnencryptedDataSourceWithDuplicates.qvd(qvd);

Qualify *;

NoConcatenate
EncryptedDataSourceWithDuplicates:
Load
    encrypt([First Name], ‘$(vEncryptionKey)’) as [Encrypted First Name],
    encrypt([Last Name], ‘$(vEncryptionKey)’) as [Encrypted Last Name],
    encrypt(Text(Date(DOB, ‘MM/DD/YYYY’)), ‘$(vEncryptionKey)’) as [Encrypted DOB],
    encrypt([SS#], ‘$(vEncryptionKey)’) as [Encrypted SS#],
    *
Resident UnencryptedDataSourceWithDuplicates;

Unqualify *;

Store EncryptedDataSourceWithDuplicates Into EncryptedDataSourceWithDuplicates.qvd(qvd);

By building mapping tables for each field using the distinct field values and their encrypted value, we can ensure QlikView does not store multiple encrypted values for the same input.

For i = 1 To NoOfFields(‘UnencryptedDataSourceWithDuplicates’)
    Let vField = FieldName($(i), ‘UnencryptedDataSourceWithDuplicates’);
    Let vMapTbl = ‘$(vField) Encrypted Map’;

    [$(vMapTbl)]:
    Mapping Load
        Distinct [$(vField)] as key,
        encrypt(‘$(=[$(vField)])’, ‘$(vEncryptionKey)’) as value
    Resident UnencryptedDataSourceWithDuplicates;
Next;

Qualify *;

EncryptedDataSourceWithDuplicatesAndDataCompression:
Load
    ApplyMap(‘First Name Encrypted Map’, [First Name], ‘ERROR’) as [Encrypted First Name],
    ApplyMap(‘Last Name Encrypted Map’, [Last Name], ‘ERROR’) as [Encrypted Last Name],
    ApplyMap(‘SS# Encrypted Map’, [SS#], ‘ERROR’) as [Encrypted SS#],
    ApplyMap(‘DOB Encrypted Map’, [DOB], ‘ERROR’) as [Encrypted DOB],
    *
Resident UnencryptedDataSourceWithDuplicates;

EncryptedDataSourceWithDuplicatesAndDataCompressionQvd:
Load
    ApplyMap(‘First Name Encrypted Map’, [First Name], ‘ERROR’) as [Encrypted First Name],
    ApplyMap(‘Last Name Encrypted Map’, [Last Name], ‘ERROR’) as [Encrypted Last Name],
    ApplyMap(‘SS# Encrypted Map’, [SS#], ‘ERROR’) as [Encrypted SS#],
    ApplyMap(‘DOB Encrypted Map’, [DOB], ‘ERROR’) as [Encrypted DOB]
Resident UnencryptedDataSourceWithDuplicates;

Unqualify *;

 // This should be roughly the same size as the UnencryptedDataSourceWithDuplicates.qvd
Store EncryptedDataSourceWithDuplicatesAndDataCompressionQvd Into EncryptedDataSourceWithDuplicatesAndDataCompression.qvd(qvd);

Drop Table EncryptedDataSourceWithDuplicatesAndDataCompressionQvd;


Recap

To recap, it is possible to encrypt and decrypt values in QlikView. I hope this post was helpful and informative. If you have any questions, please leave a comment.

A copy of the Encryption in QlikView QVW used in the examples above is available on github.

Share

No Comments

Video: Building a background image + shadow objects in QlikView

Recently on QlikView’s demo website I’ve noticed a lot of applications with abstract background patterns with accent colors, as well as shadows used within dashboards to separate different objects and areas. I have created a video to show how these effects can be approximated using just QlikView and Google, without the need for a nice graphics editor. The end result will look like this:

Example

-Speros

Share

No Comments

Magnetic Fields in QlikView (+ open source QV!)

Recently a zoology study about dog defecation has gone viral. No, seriously.

In an attempt to test dogs’ sensitivity to Earth’s magnetic field, researchers recorded the urination and defecation of a sample of dogs and plotted the angular position of their bodies during these acts against the magnetic field. The results of the study produced great visualizations like these:

 

Each dot represents the mean angle of a dog across all of it’s observations. I believe the dotted line through the middle represents either the mean of all observations and the arcs some sort of confidence interval or magnetic field spread, but I am not certain.

I reproduced the chart in QlikView using radar charts, with the dotted line as the mean and the highlighted arcs as a 95% confidence interval. Random data was used to produce the following:

Read the rest of this entry »

Share

No Comments

Showing Missing Time Data in QV

A common problem I encounter in QlikView is missing data in trend charts. Take the following table for example:

Division MonthYear Sales
A Nov 2013 100
B Nov 2013 50
A Dec 2013 123
B Dec 2013 43
A Jan 2014 109
A Feb 2014 84
B Feb 2014 60
A Mar 2014 99
B Mar 2014 58

Division B did not record any sales for Jan 2014. Therefore, a row was never entered in the table. If we try to plot this data in QlikView as a line chart, we will see the following:

Multiple Lines

Read the rest of this entry »

Share

1 Comment

QlikView Viz Hodgepodge

Here is an assortment of random ideas I’ve been playing with in QlikView recently:

Visualizing Press Sizes

Imagine you produce cardboard. You want to visualize the different sizes of cardboard you can produce against the different size machine presses you have to make the cardboard. There are two ways to accomplish this.

Scatter Chart

Scatter Chart Read the rest of this entry »

Share

No Comments

Splitting Trend Lines in QlikView

This post is a follow-up to the Strip Plots post. The Strip Plots post contains more details on the implementation of these charts. See that post for technical background.

Line charts excel at showing trending data. However, when too many lines are plotted at once, the resulting chart can be difficult to interpret:

Traditional

 

One way to alleviate this is to split the x-axis so that each line has it’s own axis with the same domain of values. In the above example, that means that dimension A would have an x-axis from 2010-2013, as would dimension B, etc. All of these lines would share the same y-axis. This can be done in QlikView using a scatter chart:

Split Line

Read the rest of this entry »

Share

5 Comments

Optimized QVD Loads: caveats for using the Exists() function

As far as I know the Exists() function, in its simplest form ( Exists([Field]) ), is the only WHERE clause you can include when loading from a QVD that will maintain an optimized load.

A very convenient technique you can use to filter data when loading from a QVD, while maintaining an optimized load, is to pre-load the field on which you want to filter with the desired value(s) and then include the Exists() function in the LOAD statement from the QVD.

There are a couple of caveats to note in order to maintain the optimized load using the Exists() function:  1) the field referenced in the Exists() function must be included in the LOAD statement, and 2) the field must not be aliased.

The follow LOAD statements give examples of incorrect ways and the correct way to filter data in a QVD LOAD statement using Exists().

// Pre-load the filter value(s)
RIFilter:
LOAD 1 as [Record Include Flag] AutoGenerate(1)
RIFilter << AUTOGENERATE(1) 1 lines fetched
// Filter field not included in LOAD field list 
FactTable: 
LOAD   
  [Effective Date] as EffDt 
FROM    
  FactTable.qvd (qvd) 
WHERE   
  Exists([Record Include Flag]) 
FactTable << FactTable 6,514,686 lines fetched <- Not optimized

 // Filter field included in LOAD field list and aliased 
FactTable: 
  LOAD   [Effective Date] as EffDt,   
  [Record Include Flag] as RIFlg 
FROM    
  FactTable.qvd (qvd) 
WHERE   
  Exists([Record Include Flag]) 
FactTable << FactTable 6,514,686 lines fetched  <- Not optimized
// Filter field included in LOAD field list and NOT aliased
FactTable:
LOAD
  [Effective Date] as EffDt,
  [Record Include Flag]
FROM 
  FactTable.qvd (qvd)
WHERE
  Exists([Record Include Flag])
FactTable << FactTable (qvd optimized) 6,514,686 lines fetched <- Optimized


NOTE:  Tested on QlikView 11.2 SR3

Share

, , ,

1 Comment

QlikView Extension Object – Utilization Planner with d3

Recently I was working on a dashboard to help myself manage my consulting time. As a consultant, my key performance metric is the percent of time I spend each week billing clients. This number is called my “billability rate”.

The dashboard I created helps me track my billability rate over time and analyze outliers. However, I also needed the ability to look forward. At the end of each quarter, my billability rate is reviewed, so it would be helpful to quickly perform a scenario analysis based on my schedule to forecast that final rate.

I created an extension object to do this which utilizes d3.js, a robust and powerful Javascript library for visualizing data. The visualization outside of QV looks like so:

Based on the most recent data entered, the chart draws a bar for each remaining work week until the end of my company’s fiscal quarter. The black line on each bar can be dragged to change the forecasted amount of hours that will be billed in each week. On the far right, two text boxes update to indicate how many billable hours have been forecasted and the effect of that forecast on my final billability rate at the end of the quarter.

In this embedded example, the data is fake and has hard-coded values for the inputs; however, in the extension object the necessary data to draw this chart is received from QlikView.

While this implementation has a very specialized use-case, I have uploaded the extension object in case people want to take a look at the code or even modify it for their own purposes. Download UtilizationPlanner.qar

-Speros

Share

2 Comments

Behavior difference between ‘Export’ and ‘Send to Excel’

Send to Excel exports the underlying numeric values, as you can see by the digits after the decimal place in the Excel Formula Bar in the lower right.

Export to any file format other than .xls (I’ve tested the majority) exports only the values you can see based on the formatting in the object you exported, potentially losing precision, as you can see in the CSV in the lower left.

It’s an important distinction to be aware of.

export-screenshot

This can be resolved locally, in QlikView Desktop, under your User Preferences, but there is no corresponding QlikView Server setting to change the Export formatting to end users.

export-no-formatting

Share

No Comments