Archive for category Miscellany

Flat, Dynamic UI Navigation in QlikView

A common requirement in a QlikView application is to custom build a tab row or sub-tab row for navigation on a sheet. There are several ways to build this functionality, with various advantages and disadvantages. One approach is to use block charts to create navigation elements like so:

Nav Elements

 

There are several advantages to this implementation:

  1. Minimalist, flat styling
  2. Dynamically driven by a loaded field of data, so can be configured easily
  3. The “button” widths are based on the size of the text, so a long string of text for 1 button does not unnecessarily alter the widths of the other elements

This solution is possible with block charts because a block chart can have bars of variable width. The above visual consists of two block charts: 1 for the underline, and 1 for the text.

The solution uses an island table called “SampleList” that loads in a list of the navigation elements. This list box is set to “Always One Selected”. The charts are coded as follows:

Text portion

Chart type: Block

Dimension: SampleList

Expression: =dual(only({1}SampleList),len( only({1}SampleList)))

Background Color: =argb(0,0,0,0)

Text Color: =if(SampleList=SampleList,rgb(50,150,200),rgb(200,200,200))

Values on Data Points: Enabled

All other chart elements, like axes and background color, are disabled or made transparent.

 

Underline portion

Chart type: Block

Dimension: SampleList

Expression: =len( only({1}SampleList))

Background Color: =if(SampleList=SampleList,rgb(50,150,200),rgb(230,230,230))

Values on Data Points: Disabled

All other chart elements, like axes and background color, are disabled or made transparent.

 

A few notes

In the Text portion, we use the dual function to put the name of the SampleList on top of the bar, as opposed to it’s width. The width is defined by the length of the text for the button, giving us the correct relative proportions between elements. Because we always have a value selected in this navigation field, we have to use the {1} reference so that our selections are ignored and all the navigation elements are included.

The Underline portion is broken out into a separate chart from the text portion so that we can have better control over the height of the blocks, which equates to the thickness of the underline, as well as the position in relation to the text. Also notice that the grey color for the inactive blocks is lighter than the grey used for the inactive text. This is because the text is much thinner than the block underline and thus requires a darker color to be legible.

One disadvantage of this approach is that the cursor on these buttons is QlikView’s chart selection cross-hair cursor. This may be annoying or confusing to users. For a mobile implementation however, this solution works well since the user will not see the cursor.

Download the example QVW here.

-Speros

Share

5 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

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

QlikView Keyboard Shortcuts

There are numerous keyboard shortcuts that make developing QlikView Applications even faster and easier. Here are some that I have found to be useful:

  • Ctrl+Shift+S – Shows all objects/tabs in the QVW, regardless of hide/show condition
  • Ctrl+Shift+D – Clear all, same as the clear button but it’s faster if you’re just typing
  • Ctrl+Shift+Q – Opens Document Support Information, which provides detailed information about the QVW and computer you’re using
  • Ctrl+Shift+B – Opens Bookmark Overview
  • Ctrl+Shift+O – Opens Connect to Server dialogue
  • Ctrl+Shift+L / Ctrl+Shift+U – Locks and unlocks whichever listbox is activated
  • Alt+Click – Holding Alt and hovering over any object allows you to move the object, which is particularly useful for tables so you don’t have to grab the caption
  • Alt+Ctrl+Click – Copies an object to another part of the sheet or another tab (NB: Allow Move/Size checkbox must be enabled in Properties > Layout if you want to move to another tab)
  • Ctrl+Alt+E – Opens Expression Overview
  • Ctrl+Alt+V – Opens Variable Overview
  • Ctrl+Alt+D – Opens Document Properties (NB: This is a good way to view and change variables if your application has many variables. The Variable Overview will calculate each Variable before opening, but the Variables tab in the Document Properties will not.)
  • Ctrl+Alt+S – Opens Sheet Properties
  • Ctrl+Q+Q – In the Script Editor, writes code that generates sample data
  • Ctrl+Q – Displays Current Selections
  • Ctrl+K+C / Ctrl+K+U – In the Script or Expression Editor, comments and uncomments the selected block of code
  • Ctrl+G – Activates Grid Mode. In addition to the obvious segmentation of the screen, Grid Mode also allows you to right-click inside one cell of a table and format that cell alone, using the Custom Format Cell option in the context menu. (NB: You may enable the Custom Format Cell option outside of Grid Mode by going to User Settings > Design > Always Show Design Menu Items).
Share

,

No Comments

“I think that Comic Sans always screams ‘fun’”

httpvh://www.youtube.com/watch?v=OFDuwguGbAc

Share

No Comments

Updated RSS aggregators for viz and QlikView

I updated the RSS aggregator feeds in the lower right and moved from Yahoo! Pipes over to Feed Rinse. Here are the current contents:

QlikView

  1. (this site)
  2. …and points beyond
  3. BI Review
  4. iQlik
  5. Living QlikView
  6. Michael Ellerbeck
  7. mindspank
  8. Official QlikView blog
  9. One Qlik at a Time
  10. Qlik Tips
  11. qlikblog.at
  12. QlikMetrics
  13. Qlikshare
  14. QlikView Addict
  15. QlikView Maven
  16. QlikView Notes
  17. QlikView Podcast
  18. Quick Inteligence
  19. Quick Qlear Qool
  20. QV Design
  21. The Qlik Board
  22. The Qlik Fix
  23. TIQView Blog

Visualization/Presentation

  1. Flowing Data
  2. Information Is Beautiful
  3. Juice Analytics
  4. Junk Charts
  5. Presentation Zen
  6. The Extreme Presentation Method
  7. The Why Axis
  8. Visual Business Intelligence
Share

No Comments

The Joy of Stats, and some less joyous stats

Hans Rosling‘s The Joy of Stats is now available in its entirety on YouTube, via FlowingData:

httpvh://www.youtube.com/watch?v=oOOmqHzkkOo

You may recognize Rosling from his 200 Countries, 200 Years, 4 Minutes video, which has gotten a lot of press – on the Internet, at least.

This dovetails nicely with the classic How to Lie with Statistics, which I just read over Christmas. It re-sharpened my eye towards deceptive visual representations, and not a day after finishing it did I see some intellectually dishonest charts on The Heritage Foundation’s Top Ten Charts of 2010.  Their #1 is pretty egregious – see if you can guess why:

top10-percent-income-earners-60031

Share

3 Comments

Edward Tufte on NPR’s On The Media

Minister of Information (follow link to embed of audio)

Edward Tufte is perhaps the country’s foremost evangelist for the clean, clear and rich presentation of complex information. The Obama administration’s stimulus package is flooding the economy with 787 billion dollars for employment and public works projects. Put the two together, as Obama did earlier this month when he nominated Tufte for the stimulus advisory board with the hopes that the public will have a fighting chance of understanding where the stimulus money went and what it’s doing.

Share

No Comments

The ultimate in information on demand

Share

No Comments

Business travel, odds and ends

Apologies for the dearth of posts lately, but I have been extremely busy with work and traveling every week for the last few months.  Apropos of that, here is a link to The Economist‘s business travel blog, Gulliver.

Coincidentally, there was a post yesterday that touched on one of the tenets of facilitating comparisons in data, ensuring that you are comparing apples to apples:

Easter and air travel

Economist Dean Baker, of the left-leaning Center for Economic and Policy Research, had a good catch last week. USA Today reported on Tuesday that airlines were seeing “signs of improvement” in their April passenger numbers. But Baker knew there was more to the story:

Come on folks, when you do year over year comparisons of air travel you have to remember things like holidays. Easter was in March last year, and April this year. This should mean that, other things equal, a year over year comparison of air travel for March will look bad and a year over year comparison for April will look good. So, why is USA Today surprised by the uptick in April travel?

That’s reporting as expected from USA Today, home of the most gag reflex-inducing data visualization in any publication I see, thanks to free copies at virtually every hotel chain in America.  Yikes:

usa-today

Also of potential interest to other business travelers are Dan Pink’s travel tips, from DanPink.com:

Intro

  1. Never get sick again
  2. Bring down da’ noise
  3. Four road food rules of thumb
  4. The rule of HAHU
  5. More hygiene!
  6. Stay connected
  7. Zip through security

Though I’m a frequent traveler, he still provided some interesting and amusing insights, like why I try desperately not to be the bottleneck in the security line at the airport (and he is right about that).

On a related note, as I mentioned on this site’s Reading page, Pink’s book, A Whole New Mind, provides some food for thought about the changing nature of the working world and skills you can acquire that will help you roll with the punches.  Oprah evidently thought highly enough of the book enough to give everybody a copy at my brother’s graduation last Spring.

And as long as I’m doing a travel post, here are a few more resources I use:

Share

1 Comment