Category Archives: Productivity

Productivity, Qlik Sense, QlikView, Scripting, Tools

Storing a Data Model in a Single QVD

18 Comments

Have you ever thought it might be interesting to store a  Qlik data model into a single QVD?  This can be useful in a number of cases such as:

  • Archiving (and retrieving) data models.
  • Overcoming the “single binary load” restriction.

QlikView Components (QVC) Version 11 introduced two new routines to do just that:

Qvc.ExportModel — Exports all tables of the current model into a single QVD.

Qvc.ImportModel — Import a data model created by Qvc.ExportModel.

Even if you don’t have QVC V11 installed, you can try Qvc.ExportModel right now using  http include.  Add these lines to any QlikView script (instructions for Qlik Sense further on down in this post).

$(Must_Include=https://github.com/RobWunderlich/Qlikview-Components/releases/download/v11.1/Qvc.qvs);
 CALL Qvc.ExportModel
 CALL Qvc.Cleanup

Mind the wrap. The Must_Include should be on one line. Using QVC requires the Qvc.qvs library be included (usually at the beginning of script), CALLing Qvc routines, and CALLing a Cleanup routine at the end of your script.

Assuming this script is included in “Sales Dash.qvw”,  the default exported model QVD will be named “Sales Dash.qvd” in the same directory.

 

Now, to import this QVD model into another qvw, replace the CALL to ExportModel in the above sample with:

CALL Qvc.ImportModel('Sales Dash.qvd')

The original model will be reconstructed as individual tables.

Qvc.ExportModel has three optional parameters:

CALL Qvc.ExportModel(['qvddir'],['qvdname'],['addTimestamp']);
Parameter Number Parameter Description
1 String. Optional. Relative or absolute directory where the model QVD will be stored. If relative, it follows the same rules as the STORE script statement for relative directory.
2 String, Optional. Name for the model QVD. If omitted, the name of the QVW will be used. For example, if QVW is “Sales.qvw”, then QVD will be “Sales.qvd”.
3 String, Optional. 1/0 True/False. If True, a timestamp of the form _YYYYMMDDhhmmss will be appended to the QVD name. Default if omitted is False.

 

Qlik Sense has no default path  so parameter #1, a lib:// for the QVD should be specified.  Alternatively, if a lib has been established with a DIRECTORY statement, parameter 1 can be omitted.

Qlik Sense will require a web file Connection for the http Must_Include.

webfile-connection

After defining the web connection and having an appropriate folder connection to store the QVD in,  Qlik Sense script would look like this:

$(Must_Include=lib://QvcWeb);
 Call Qvc.ExportModel('lib://QVData')
 CALL Qvc.Cleanup;

 

That’s all there is to it!  If you are already using QVC, I hope you’ll find these routines a welcome addition to the library.  If you are new to QC, explore more at QlikviewComponents.org.

-Rob

Thanks to Jörgen Peerik for raising the single-QVD export idea during a QVC class. 

 

Share
Productivity, QlikView, Scripting, Tools

Preparing your script for QV12

1 Comment

Summary: I provide a tool to check your script for compatibility with QlikView version 12. 

I’ve blogged about a couple of script changes in QV12 here.  Since then I’ve also noticed that the $(include) statement is also affected by the Directory statement. That is, if the script below works in QV11, it will not work in QV12:

DIRECTORY Data;
 $(Must_Include=config.txt);

This is because QV11 looks for the file in the working directory where the QVW is, whereas QV12 will respect the DIRECTORY statement and look in the Data directory.

To summarize compatabilty considerations for QV12:

How will you know if you have existing  script that may be impacted by these changes in QV12?  In an earlier post, I introduced the Script Repository tool which can be used to search script across QVWs.

You can use the tool to  search for potential issues.  But I thought I would make it a bit easier by adding a dedicated “Version 12 Upgrade Check” sheet that does the searching and highlighting for you.

The chart at the top of the sheet will list any document that has script that should be examined further.  Select a document, press the highlight button and the script of interest will be outlined in yellow.

My guess is that most customers will not have any compatibility issues.  But why take chances?  Be a hero and scan your script before upgrading.

-Rob

Join me at an upcoming Masters Summit for Qlik event in Johannesburg (6-8 Sept) or Austin (10-12 Oct).  In my Advanced Scripting session, in addition to teaching important scripting techniques, we’ll look at methods and tools for managing your “script farm”. 

Share
Productivity, QlikView, Visualization

Alt States Merged Selections Tip

4 Comments

Summary: I suggest a simpler syntax for merging selections from multiple states. 

You may be familiar with the “Product Grouping” example from the “What’s new in QlikView 11.qvw” sample.  It’s a great  beginner demo of using Alternate States for comparative analysis.

In this visualization, a user can select two sets of values from the [Product Sub Group] field. The two different sets are represented by two states, [Group 1] and [Group 2].  The blue [Group 1] bar is plotted on the bar chart using this expression:

sum({[Group 1]<Region = $::Region, [Sales Rep] = $::[Sales Rep], Path = $::Path, Year = $::Year, Quarter = $::Quarter, Month = $::Month>} Sales)

[Group 1]  as the Set Identifier indicates we want to start with the selections in [Group 1]. We then modify, or add, selections from the default state by referencing each field with the “$::fieldname” syntax.

The green [Group 2] bar is created with a similar expression, the only difference being [Group 2] as the Set Identifier.

The “$::fieldname” syntax works, but it forces us to list every field. Listing every field can get difficult. Is there an easier, more generic method?  Yes, if we redefine the problem as:  All selections from the Default state except for [Product Sub Group].

sum({[Group 1] * $<[Product Sub Group]=>} Sales)

Breaking the Set Expression down:

[Group 1]    // Group 1 selections
*    // Intersected with
$<[Product Sub Group]=>   // All selections from Default except [Product Sub Group]

I’m not suggesting  the sample is wrong. The “$::” syntax is useful to know and is required when you want to reference only specific fields.  I’m posting this alternative because I see people copying this more complex $::  syntax when the simpler syntax would suit their application.

-Rob

Join me at an upcoming Masters Summit for Qlik event in Johannesburg (6-8 Sept) or Austin (10-12 Oct).  Oleg Troyansky’s Set Analysis and Advanced Aggregation session provides  more useful tips and advanced techniques in using Alternate States.

 

 

Share
Productivity, Qlik Sense, QlikView, Scripting

Touchless Formatting

22 Comments

Summary: I show a scripting technique to assign display formats to loaded data without touching existing load statements. 

I coded in  SAS for many years and always appreciated the FORMAT statement which allows  assigning a display format to a field, independent of loading the field.

FORMAT OrderDate MM/DD/YYYY;

In QlikView and Qlik Sense script, there is an  equivalent that is useful to be aware of.   It’s not a statement, but a little known trick (so little known I’ve never seen anyone but me do it, although I’m sure others have thought if it).

// Load some dummy fields just to assign formats
TempFormatTable:
LOAD
 Date(0, 'MM/DD/YYYY') as OrderDate,
 Date(0, 'MM/DD/YYYY') as ShipDate,
 Num(0, '00000') as PostalCode,
 Num(0, '#,##0.00') as OrderTotal
AutoGenerate 1;

Facts: // Load the QVD
LOAD * FROM data1.qvd (qvd);

DROP TABLE TempFormatTable;  // Drop temp table

The formats assigned in the TempFormatTable will be inherited by any like-named fields in the QVD Load.   I sometimes find this easier than adding formatting function to the QVD Load statement because:

  • It maintains the optimized QVD load.
  • I can include a master list in the TempFormatTable. There is no error if a field doesn’t exist in the QVD.
  • Syntactically simpler.
  • I don’t touch the existing Load statement.

I don’t always format this way, but there are a number of scenarios where the technique is useful. A common application is to change formats from one locale to another. For example, loading a QVD created in Europe (with European formats) and assigning US Date and Number formats.

The technique works for any input source;  SQL, QVD, xls, etc. It works for both QlikView and Qlik Sense.

You may not ever need this tip, but if you do, I hope it saves you some time and makes your coding easier.

-Rob

Want more Tips & Tricks? Join me at an upcoming Masters Summit for Qlik event in Johannesburg (6-8 Sept) or Austin (10-12 Oct).  In addition to our two days of core sessions, Bill Lay’s “Tips & Tricks” on Day 3 always teaches me something new.  

Share
Productivity, QlikView, Scripting, Visualization

Scaling Numbers and DSE Tips

7 Comments

Summary: I demonstrate a simple reusable expression to auto scale numbers in QlikView. This leads to an exploration of some of the finer details of dollar sign expansion.

A QVW example to accompany this post is available for download at http://qlikviewcookbook.com/wp-content/uploads/2016/05/ScalingNumbers.qvw.

The QlikView auto-scaling feature selects an appropriate unit – billion, million, thousands — based on the magnitude of the  Y-axis values.  It’s a nice feature  available in Line and Bar charts.  How can we create the same functionality in Text Objects or Straight Tables?

It’s easy enough to use an if() function that tests the magnitude, does any necessary division, and formats appropriately. For example:

if(Sum(Sales)>1E6, num(Sum(Sales)/1E6,'$#,##0.000M')
 ,if(Sum(Sales)>1E3, num(Sum(Sales)/1E3,'$#,##0.000K')
 ,num(Sum(Sales),'$#,##0')
 ))

(The 1E6 is an easier way to write 1000000).

To avoid repeated coding of “Sum(Sales)” I create a reusable variable with parameters in the script like this:

SET vScaleNumber=if($1>1E6, num($1/1E6,'$#,##0.000M')
 ,if($1>1E3, num($1/1E3,'$#,##0.000K')
 ,num($1,'$#,##0')
 ));

Now I can use the variable vScaleNumber in a Text Object as:

=$(vScaleNumber(Sum(Sales)))

The string “Sum(Sales)” will get substituted in  every occurrence of “$1”.  I ‘ll get an appropriately formatted number like:

If I use “$(vScaleNumber(Sum(Sales)))” in a Straight Table expression without label, hovering over the column heading will show me the full substitution in  a tooltip.

I  can see that the “$1” substitution occurs before the expression is evaluated, and the substituted expression looks like:

if(Sum(Sales)>1E6, num(Sum(Sales)/1E6,'$#,##0.000M')
 ,if(Sum(Sales)>1E3, num(Sum(Sales)/1E3,'$#,##0.000K')
 ,num(Sum(Sales),'$#,##0')
 ))

I’ve avoided re-typing “Sum(Sales)”. But I may have a concern about the performance implications of repeated execution of “Sum(Sales)”.  And what about more complex expressions such as “Round(Sum(Sales),10)”?  The comma in that expression will break the syntax as variable parameters always treat commas as parameter separators.

I can fix the comma/performance problem by using Dollar Sign Expansion (DSE) with an “=”.  The “=” will cause the expression to evaluate and pass the numerical result to vScaleNumber.

=$(vScaleNumber($(=round(Sum(Sales),10))))

Checking the expansion in a Straight Table shows:

if(1783150>1E6, num(1783150/1E6,'$#,##0.000M')
,if(1783150>1E3, num(1783150/1E3,'$#,##0.000K')
,num(1783150,'$#,##0')
))

I  see the value of “round(Sum(Sales),10)” has been calculated as “1783150”,  yielding an efficient and syntactically correct expression.

Next  I’ll add a Dimension to the Straight Table.  The row results are incorrect!

The “=” in the DSE caused the Sum expression  to be evaluated only once for the entire chart, yielding the same value for every row.  How to fix?

I will calculate the sum() expression in a n Expression n column, and then hide this column on the Presentation tab. I can then refer to the hidden column:

=$(vScaleNumber(Column(1)))

Once again, the expansion yields an efficient and syntactically correct expression.

if(Column(1)>1E6, num(Column(1)/1E6,'$#,##0.000M')
 ,if(Column(1)>1E3, num(Column(1)/1E3,'$#,##0.000K')
 ,num(Column(1),'$#,##0')
 ))

I started this post by demonstrating a simple expression to format scaled numbers. It’s a function I frequently use.

For more on DSE, see Henric’s post at https://community.qlik.com/blogs/qlikviewdesignblog/2013/11/18/dollar-expansions

A QVW example to accompany this post is available for download at http://qlikviewcookbook.com/wp-content/uploads/2016/05/ScalingNumbers.qvw.

-Rob

Share
General, Productivity, Visualization

Yoke Dashboard

1 Comment

I was chatting with a colleague recently about trends in BI and I brought up what I call the “commoditization of metrics” .  Google Analytics is an early example of this — your data crunched and delivered at the KPI level.

I recently ran across a great example of the commodity metrics idea:  Yoke.io.

Yoke let’s you build your own dashboard using metrics gleaned from cloud services such as Gmail, Twitter and Github. Here’s a portion of my Yoke dashboard. It’s all built with a few clicks and no coding.

Yoke Image

Give Yoke.io a try, it’s free!

-Rob

Join me at the upcoming “Masters Summit for Qlik” in Milan on 5-7 April. In addition to learning about all things Qlik, we’ll be talking about trends in Dashboarding and BI. 

Share
Productivity

What is BI Self Service?

5 Comments

A lot of the buzz around Qlik Sense is “Self Service” capabilities. Some people assume from that buzz that QlikView is  not so good with Self Service. But what is BI “Self Service” anyways?

The goal of BI is to provide answers and insights to the business. Those answers come from interacting with content, not creating content.  A reasonably well designed QlikView application used by someone with a small amount of training has always provided great Self Service.  That is, if you define Self Service as a business user being able to quickly answer new  business questions without engaging a developer.

Let’s look at an  scenario. The Canadian government has proposed a 10% tariff on the very class of goods you sell.  What might this tariff mean to your company? Quick — how much do you sell to Canada? If you pass this increase directly to the customer, how many customers do you need to talk to?

Do you require a chart of “Sales by Country” to answer this question? If you can select Country=Canada somewhere in the app, can you use the Total line from the “Sales by Year” chart to answer the question?

What if there is no Country listbox? Do you need to create one? Or can  you use associative search, a search object, advanced search or a Country dropdown in a chart to make the selection?

QlikView can answer a broad range of questions with just a few charts and listboxes.

I’m not knocking Qlik Sense. The same excellent Self Service capabilities are available there with the added bonus that content creation, when required, is much easier. Plus you get consistent implementaion of current selections and global search.

I  do think that an opportunity is overlooked if we first imagine Self Service as content creation rather than interaction.   I enjoy teaching the two day QV Designer class to business users.   However, I think many  of the students may be better served by a two hour class that focuses on how to navigate QlikView well.

I don’t think of a “Power User” as someone who creates charts but rather someone who knows how to search, select and  associate.   A Power User doesn’t write a Set Analysis expression. They make a series of selections and store it in a bookmark.

What comes to your mind when you think “Self Service BI”?

-Rob

 

Share
Productivity, Visualization

Using SR11 “SilentErrorInChart” Switch

5 Comments

Last month I wrote about error handling changes in Qlikview SR11. Today I want to relate my experience in using the new “SilentErrorInChart” switch during development.

Let’s start with an excerpt from the SR11 Release Notes:

As a result of fixing bug 69228 “Syntax checker not working”, syntax error messages in object expressions are now returned to objects instead of returned as NULL. This may result in an object which rendered in SR10 or earlier, displaying an error message in SR11 (”Error in expression”).

This feature changes what you see — if a chart column has an error. Prior to QV11,  the chart would render, but values in the error column would display a null (“-“):

With SR11, the default behavior is to not render the chart and instead display a chart level error message:

This is useful. It clearly raises the flag that one of this chart’s expressions is returning an error. But which expression? You will need to go through the expressions one by one, and if there is more than one column in error, you will repeat the exercise.

Can I return to the pre-SR11 behavior? Where the chart renders what columns it can, so I can identify what columns are returning errors? Yes! There is a setting for that.

The setting is not (yet) configurable through the User Preferences Dialog. So we need to use the backdoor “easter egg” to modify the setting. To reach the easter egg settings dialog, select “Help. About QlikView”  and then Right-Click on the QV bullseye logo in the lower left of the dialog box.

 

In the Settings list, scroll down and select (left click) “SilentErrorInChart” to display or modify the current setting.

A Value of “0” for this setting means to use the new SR11 behavior. That is, any column in error will cause the entire chart to not render.

A Value of “1” will return to the pre-SR11 behavior. QV will render what columns it can, and display “-” for those columns with errors.

To modify the setting, overtype the Value, press the Set button followed by the Close button. There is no need to exit QV to recognize a change for this setting. However, the chart must be “re-rendered” to utilize the setting. Easiest method I have found is to edit and save (“OK”) the chart Properties.

I’m finding that during most development, I set “SilentErrorInChart” to “1”. I want to identify columns in error as I create them.

In my final pre-production check, I’m finding that changing “SilentErrorInChart” to “0” is a useful quality control check to dramatically surface any chart expression problems.

-Rob

Share
Productivity, Scripting

Qlikview Components (QVC) Training June 4

6 Comments

I’ll be leading a 3.5  hour training June 4 on using the free open source “QlikView Components” scripting library.   The training is held on-line and you can find out more and register at this link on the Qlik-On website.

QV Developers around the world use QVC in their scripting to:

  • Save time.
  • Improve Quality.
  • Implement Advanced Functions.
  • Get it Right the First Time.

Create a master calendar? No problem — one line of script:

CALL Qvc.CalendarFromField('OrderDate');

Fiscal calendar that begins on Month 4? Again no problem:

CALL Qvc.CalendarFromField('OrderDate','','',4);

What was that Set Analysis syntax for month-to-date in the previous year? The Calendar generates a series of variables for common period to period analysis.

=Sum($(vSetPreviousYearMTD) Sales)

That’s all there is to it. Even if you add fields to the Calendar. Even if you use Italian or Norwegian for the calendar field names.

Quick! Write the script to create an AsOf table for field Year-Month.  I’m waiting…   I can’t do it either without rooting around for an old project to copy from. But I can write:

CALL Qvc.AsOfTable('Year-Month');

Here’s are a few more things QVC can do:

  • Load variables from an external file.
  • Load custom icons from a folder.
  • Logging, including rolling external files.
  • Pause mid-script and let you inspect the contents of a table.
  • Incremental Reloads!
  • NVL!

The QVC project was founded in 2012 with the goal of simplifying common scripting tasks and implementing best practices.  The focus is on what we call the “big middle” of scripting tasks. 

The library is continually refined and enhanced with input from QV developers around the world and we expect some exciting new testing routines soon!

I hope you can join me June 4 to learn the details of installing and using QVC. You will leave the class with the confidence and skills to use all the QVC routines and begin supercharging your script!

-Rob

 

 

 

Share
Productivity

Name That QVW

Leave a comment

Recommendation: Give all your QVW files unique names.  Do not, for example,  maintain both;

Sales\Dashboard.qvw
Finance\Dashboard.qvw

Instead do:

Sales\SalesDashboard.qvw
Finance\FinanceDashboard.qvw

If you want to stop here at TLDR (Too Long Didn’t Read) just take the recommendation. Use unique names. For the geek crowd, read on,

The reason for preferring globally unique names is metadata.  QlikView provides a number of different tools and facilities for scanning QVWs that provide useful (metadata) information for managing your pool of QV documents. Among them are:

– The lineage elements in a QVD that tell you what QVW created this QVD.

– QvsAdminDataProvider.dll that exposes information on user created server objects and ownership.

-QvServerObjectConnector.exe that exposes information on the Dimensions and Measures used in those server objects.

– A scanner you may write yourself (or one of my tools) that reads information from QVD or QVW files.

Each of those providers may use a different full path representation for the QVW file.  These differences result in a different  “identity” for the QVW and create difficulties in coalescing these very useful bits of information together to the common QVW file.

The lineage elements record the path at reload time, which is probably the server path, which does not align with the mapped drive you use in your scanner app.

QvsAdminDataProvider uses Access Point paths, which are URL paths like:
Dist/Sales/Dashboard.qvw

QvServerObjectConnector uses physical paths to the .shared file which are like:
X:\qlikvewserver\DistributedDocuments\Sales\Dashboard.qvw

These different path points of view make it difficult to tie the information together. If we can assume that the qvw name “Dashboard.qvw” is an identifying key, there is much insight available by combining these various metadata sources,

Bottom line. Give your QVW files globally unique name to get the most leverage of the metadata.

-Rob

There are still seats available for the San Francisco Master Summit for QlikView. Join us to learn about Advanced Scripting with me, Visualization Techniques with Bill Lay, Data Modeling with Barry Harmsen and Advanced Set Analysis with Oleg Troyansky.  I’ll also be showing off some cool analysis of metadata such as what Dimensions and Expressions your users are using in their Server Objects. 

 

 

Share