All posts by Rob Wunderlich

General

Qlik Sense Opportunity for QlikView Customers

2 Comments

I had a great time participating in the Hackathon at the recent Qlik World Conference in Orlando. The Hackathon challenge was to create a visualization solution to a United Nations challenge using the Qik Sense Mashup APIs. It was a stimulating exercise for a number of reasons — the subject matter, the company of talented practitioners, and the Sense API ease of use and powerful functionality.

Through my experience with using the APIs and talking with a number of customers at the conference, I see a clear opportunity for existing QlikView customers to leverage Sense as an Extranet or Mashup solution.

Many QlikView customers have apps that currently deliver great value inside the company. These apps may be mature, data dense and backed by a sophisticated data model. Everyone is happy, but there may be some discussion about providing a subset of the solution to external users like B2B customers over the web.   That has been  possible with QlikView, but it can be to be complex to implement. It’s much easier with Sense and I think Sense could be the killer solution for extranet reuse. Here’s why:

  • The existing QlikView data model can be reused by Sense with a simple Binary Load. No need to write additional script and you maintain a single-version-of-truth for both internal and external users.
  • The sense charts and visualizations are much more “webby” in appearance and behavior,  meeting the modern expectations of web users and web developers alike.
  • The APIs are powerful, clearly designed and will be easily understood by web developers tasked with integrating Qlik content and data into portals or other  interface.
  • Better APIs to get data from the Qlik engine. For example, there are methods to create and return “hypercubes” of data. Pass in Dimensions and Expressions and calculated data is returned — without the need to create a chart. The returned data can be visualized or used to drive another process.
  • The new Session Authentication API can delegate user authentication to an existing portal session, eliminating the need for a piggybacking WebTicketing exchange. This satisfies the common customer requirement that “when the portal session ends, the Qlik session should end”.

-Rob

 

Share
Visualization

Listbox Color Chips

8 Comments

I was recently poking around in the QlikView 11 demo “Social Media Data Analysis“.  This app was first brought to my attention in the excellent blog post “Use of a Silent Legend” by  Jennell McIntire.  I like how color is used to quickly create linkage between elements on the sheet. It’s one of my favorite tips.

Looking at the mechanics of the color & object construction, I thought that while fine for a demo, there were a few things I would do differently. One item in particular — the Company listbox — stood out because of a new technique available in QV11.

The listbox contains a color chip for each Company that maps to the color used in the charts. Very effective.

The color chips are constructed as 6 individual Text Objects. This is maybe the only possible way to get that particular effect in a V10 listbox.

However, that means that I, the challenged artist, have to line those boxes up and keep them aligned. It also means that I have to be very sensitive to the contents of the listbox changing.

V11 introduced Listbox Expressions which opens up a new option.  I took the individual color boxes and put them in  jpg files. I bundle loaded (embed in the qvw) the images with this script:

Images:
bundle info load * inline [
Company, image
Company A,image\Square_Orange.jpg
Company B,image\Square_Blue.jpg
Company C,image\Square_Green.jpg
Company D,image\Square_Brown.jpg
Company E,image\Square_Red.jpg
My Company,image\Square_Purple.jpg
];

Next step was to add this Expression to the listbox:

='qmem://Company/' & Company

and select “Image” for the Expression Representation.

And now my color chips are an integral part of the listbox. Also, the chips now disappear for excluded values which is consistent with the visual behavior of the rest of the sheet.

             

I won’t be surprised if commenters come up with an even better way 🙂

-Rob

Share
Scripting, Visualization

Qvc Gets Colorful

2 Comments

Qlikview allows customization of the basic eighteen color palette at the Document or User level through property dialogs. For color control beyond the palette,  many developers utilize variables.

The set of colors and variables I use for a document or project I refer to as a “color theme”.  I’ve built up several themes over the years as well as generously borrowed theme ideas from others.

Wait! Isn’t there an single ideal theme we should all use? No. I find a regular need to update or craft new themes in response to client requirements, the latest understanding of best practices, and frankly, fashion. 

In V9 of the Qlikview Components (Qvc) scripting library I’ve added a color theming routine.  The SUB Qvc.ColorTheme(themeFile) uses an external file to define color variables. The variables are then referenced by chart objects to implement the theme.

Implementing a color theme with Qvc requires two actions; assigning colors to the variables and assigning the variables to sheet objects.

Qvv.ColorTheme takes it’s input from an xlsx or xlsm file that contains at least these two columns:

ColorVariable — A variable name. Any variable name matching the pattern Qvc.Color.v.* will be kept by the routine. Other variable names may be used as intermediate names to compute final values.

ColorValue — a valid Qlikview script color function such as rgb(0,0,0) or white().

A sample theme is provided with the Qvc distribution in etc\Colors\ColorSample1.xlsm. This sample file also offers the option to use the Excel color picker to assign a color.

In the sample file you’ll see:

Specific colors defined early in the file that are referenced later:

“Logical” color variables that define “good” and “bad” things. 

“Object Attributes” that define items like the chart 18 color palette.

After including the qvc.qvs runtime we can add the script statement

CALL Qvc.ColorTheme('ColorSample1.xlsm')

and then reload. Great! Now our document contains a bunch of Qvc.Color.v.* variables with proper color values. How do we tell our object color properties to use those variables? Do we have to type them in each object? No.

Also included with the Qvc distribution is etc\QvcColor.qvt, a QV theme file that assigns the Qvc.Color.* variable names to object definition. You apply this qvt theme to your Document as you would any other qvt theme, using the “Layout, Apply Theme” button. Apply Theme is available at the Document level (Settings, Document Properties) or at the individual object level on the layout tab.

After pressing “Apply Theme”, select and apply the etc\QvcColors.qvt file. After Apply, you’ll see that color properties in the object (or all objects if done in Document Settings) reference the Qvc.Color.* properties.

Changes to the theme file will be reflected in the object colors after reload.

You may also assign the QvcColor.qvt theme as the default document theme for new objects in “Settings, Document Properties, Presentation”. This is particularly useful if you are starting a new document.

If you develop a color theme you find useful, please let me know on the QVC User Forum and I’ll include your theme as a sample in a future release.

-Rob

I’m now teaching a 3 hour on-line course on using the QVC library at http://q-on.bi/. The next course is scheduled for 7 Nov. See http://q-on.bi/courses/qlikview-components-scripting-library/ for details.

Share
Scripting

Preceding Load is Elegant

11 Comments

I love the preceding load feature of Qlikview scripting. It can make complex things simple.

Consider for example, having to parse an arbitrary number of key:value pairs from input like:

Name:Shoes, Size:L, Color:Blue Suede, Stock: 200
Name:Socks, Model:Mens Casual, Stock:0, Color:Black
Name:Pants, Error: No attributes found

We want each key to become a Field populated with it’s matching value. Here’s the entire script to do just that.  A preceding load is a series of chained LOADs that execute from the bottom up — so read the script from the bottom.

And now we can build a chart like this.

Simple. Elegant.

I’ll be showing some other cool examples of Preceding Load in my “Advanced Scripting” session at the Masters Summit for Qlikview Oct 1.

-Rob

Script suitable for copy/paste

data:
// 4. Generic Load to transpose Key to Field
Generic LOAD RecId, Key, Value
;
// 3. Separate key & value
LOAD
RecId,
subfield(Pair,':',1) as Key,
subfield(Pair,':',2) as Value
;
// 2. Break out each key:value pair
LOAD
RecId,
subfield(Input,',') as Pair
;
// 1. Load the raw Input
LOAD *, RecNo() as RecId INLINE [
Input
Name:Shoes, Size:L, Color:Blue Suede, Stock: 200
Name:Socks, Model:Mens Casual, Stock:0, Color:Black
Name:Pants, Error: No attributes found
] (delimiter is '|')
;

Share
Visualization

Expressionless and Dimensionless Charts

13 Comments

We usually think of charts as having both Dimensions and Expressions. But that’s not always the case. Sometimes it’s useful to have only one or the other.

A Straight Table with Dimensions and no Expressions is very similar to a Table Box. Unlike a Table Box, you can add Expressions later if needed and you also get improved styling options.

 

 

 

 

 

The trick to getting rows to display when there are no Expressions is to uncheck “Suppress Zero-Values” on the chart properties Presentation pane.

 

 

 

 

 

 

 

 

A Dimensions only Pivot Table is a useful way to display a hierarchy. This can look very nice with the Indent style.

 

 

 

 

 

 

Charts may also contain Expressions only — no Dimensions. Here’s a Straight Table with two expressions and no Dimensions.

 

 

We get a single row table — the sum of all data — along with a redundant Total line. By turning off the Totals and doing some styling, we can build a nice KPI display in a single table without needing to line up multiple text objects.

 

 

 

 

We get the full power of Straight table layout and styling, number formats, visual cues etc. I flipped the columns to the horizontal by checking “Horizontal” on the Presentation pane.

Bar Charts may also be created without Dimensions. Each Expression is plotted as a separate bar, in either the grouped or stacked style.

 

 

 

 

 

 

When an Expression only Bar Chart is stacked and oriented horizontal, it can be an  interesting alternative to the Linear Gauge chart.

Free your mind from the idea that a chart always has Dimensions and Expressions and have some fun.
-Rob
Share
Scripting

Becoming a Scripting Ninja

6 Comments

As a QV Consultant, I split my time between delivering training and QV Practice.  I like both sides of the business, but I particularly enjoy contributing to the excitement of using QV that results from training.

During a lunch break today, I mentioned the Masters Summit for Qlikview to one of my students. He asked “What does it take to become a Master?”. Of course I answered “Attend the summit! “.  But it reminded me of a list I present when delivering  Qlikview Developer (Scripting & Data Modeling) training.

Most of us experienced instructors augment the standard curriculum with our own real-world experiences. When teaching scripting, in addition to surveying the range of scripting statements and modeling solutions, I emphasize three specific scripting features as “Ninja skills” — script features whose mastery makes you deadly effective. My “Scripting Ninja” list is:

1. MAPPING LOAD. Understanding how to create a Mapping table. How to use that table in all it’s forms — ApplyMap(), MAP USING, RENAME USING and the other metadata USINGs — TAG and COMMENT.

2. IntervalMatch — has many uses  including Slowly Changing dimensions, Currency Rates, Dimension Bucketing to name a few.

3. Preceding Load — can be used to create powerful parsing scripts and simplify the maintenance and accuracy of script. Here’s a primer on Preceding Load.

Those are the three special QV scripting features that stand out for me as exceptionally powerful and well worth mastering. Are there others you would add to the Ninja list?

-Rob

 

Share
Tools

Major Update to Document Analyzer

9 Comments

I’ve been publishing the free Qlikview “Document Analyzer” tool since 2009. I’ve just released a major update to DocAnalyzer. You can download Document Analyzer V2 from the Qlikview Cookbook website

DocAnalyzer is a tool to help tune the performance — response time and resource requirements — of a Qlikview QVW. DocAnalyzer can identify unused Fields and pinpoint slow charts.

How do you use DocAnalyzer? After identifying the QVW to be analyzed. press the “Extract Metadata” button to begin the analysis process. When the extract is complete, you’ll receive a popup message. The extract can take anywhere from a few seconds to several minutes for documents with many thousands of objects.

 

 

When the extract is complete, you must run a Reload of Document Analyzer. A button is provided for this or you can do Ctrl-R.

What can you discover in the analysis? Here are a few items.

– The Fields sheet will give you an overview of what fields are used and unused and where they are used — Dimensions, Expressions, Variables, etc.

A Listbox is provided with suggested DROP FIELD script statements for the unused fields.

Select $date and $timestamp in “Field Tag” listbox. Examine the “Symbols Size” column in the Field References chart. Dates and Timestamps can be stored in 8 bytes, but frequently get converted to longer string representations during loading. The impact is only significant if there are a large amount of Distinct Values.

– The Memory sheet provides an overview and detail of RAM requirements and estimates how much memory could be saved by dropping unused fields.

 

 

 

 

– The Object sheet lists all Objects along with their Calc Time and RAM Size.

 

 

 

 

Selecting a single object will enable a “Goto Object” that open the target document and Activate that object so you can examine it’s properties.

There are many other areas you can examine such as the use and performance of Calculated Dimensions, Groups, Expression contents and what properties use those expressions.

Document Analyzer is one of the Tools that Oleg Troyansky will present in his “Performance Tuning” session at the upcoming Masters Summit for Qlikview in Amsterdam Oct 1. I hope you can join us there for more performance tips and a chance to learn advanced Qlikview development skills.

-Rob

Share
Visualization

The Third Format

11 Comments

In QV number formats we can use specify different patterns for positive and negative values. The two patterns are separated by a semicolon like this:  positive;negative

#,##0;-#,##0

Did you know a third pattern can be specified? It’s used for zero values.

#,##0;-#,##0;–

Using the above format zero displays as a double hyphen “–“.  Entering nothing after the second semicolon will result in zero being displayed as a blank.

The Qlikview Reference Guide appears to make no mention of the second and third formats. However, the formats look like Excel custom formats. Using that documentation, you’ll see that text may be used to create interesting formats like:

+#,##0.00% Increase;-#,##0.00% Decrease;No Change

Which when combined with Visual Cues can produce chart output like:

-Rob

Share