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.

TAGS: QlikView, Load Script, Miscellany

Related News

IS YOUR COMPANY UPGRADING TO QLIKVIEW 12?

Axis Group was approached by a client asking for help...

Read More

Part 1 of this series describes what Qlik Sense does better than QlikView, from the perspective...

Read More