Category Archives: Qlik Sense

Qlik Sense Document Analyzer v1.4

Version 1.4 of the Qlik Sense Document Analyzer (QSDA) tool is now available for download here.

If you are not familiar with QSDA, it’s a free application profiling tool for Qlik Sense that can help you identify items such as unused fields, poorly performing expressions and data model problems.

I’m pleased that Mike Steedle of Axis Group has joined me as co-developer of QSDA.   Mike contributes his many years of experience in profiling and tuning Qlik applications.

 

Here’s what’s new in version 1.4:

  1. Installer improvements. The correct directory for Connectors and Apps is automatically detected.
  2. New attribute “ObjectIsExtension”. Possible values are:
    • False – not an extension.
    • True – object is an extension but not a widget.
    • Widget – object is a widget.
    • Missing – object is an extension, but extension is not present on server.
  3. Unused Master Visualizations, and the Dimensions and Expressions therein, are now extracted and processed.
  4. DimensionLabel field added to “Dimensions” table.
  5. New table “Bookmarks”. Bookmarks are now extracted and linked to Fields.
  6. MB Constant changed from 1000*1000 to 1024*1024. This means these numbers now scaled in MB will have slightly smaller values than previous versions.
  7. Calc Time is now displayed in seconds instead of milliseconds.
  8. Dimensions and Expressions summary added to Objects sheet.
  9. Some analysis of the data model is performed, and the results and recommendations expressed as flag fields. New sheet “Flag Summary” will display an overview of found conditions.
  10. Color highlighting of detected problems.
  11. Reorganization of sheets and sheet layouts.
  12. Addition of a “Glossary” sheet provides descriptions for flags.

The installer currently only supports installation on Qlik Sense Desktop, although you can  analyze applications on Enterprise Server.

Improving the server analysis capabilities and possibly a server install will be a focus of the next update.

If you have general usage questions on QSDA, please use the comments section here or Qlik Community.

If you have a bug to report, use the issue tracker.

I hope you find QSDA useful.  I’m excited to see it maturting and pleased to have the help from Axis Group.

-Rob

 

 

Share

Loading Varying Column Names

Summary:  A script pattern to wildcard load from multiple files when the column names vary and you want to harmonize the final fieldnames.  Download example file here.

I’m sometimes wondering “what’s the use case for the script ALIAS statement?”.  Here’s a useful example.

Imagine you have a number of text files to load; for example extract files from different regions.  The files are similar but have slight differences in field name spelling.   For example the US-English files use “Address” for a field name, the German file “Adresse” to represent the same field and the Spanish file “Dirección”.

We want to harmonize these different spellings so we have a single field in our final loaded table.  While we could code up individual load statements with “as xxx” clause to handle the rename, that approach could be difficult to maintain with many variations.  Ideally we want to load all files in a single load statement and describe any differences in a clear structure.  That’s where ALIAS is useful.  Before we load the files, use a set of ALIAS statements only for the fields we need to rename.

ALIAS Adresse as Address;
ALIAS Dirección as Address;
ALIAS Estado as Status;

The ALIAS will apply the equivalent “as” clause to those fields if found in a Load.

We can now load the files using wildcard “*” for both the fieldlist and the filename:

Clients:
LOAD *
FROM addr*.csv (ansi, txt, delimiter is ',', embedded labels, msq)
;

It’s magic I tell you!

What if the files have some extra fields picked up by “LOAD *” that we don’t want?  It’s also possible that the files have different numbers of fields in which case automatic concatenation won’t work.  We would get some number of “Client-n” tables which is incorrect.

First we will add the Concatenate keyword to force all files to be loaded into a single table.   As the table doesn’t exist, the script will error with “table not found” unless we are clever.  Here is my workaround for that problem.

Clients:
LOAD 0 as DummyField AutoGenerate 0;
Concatenate (Clients)
LOAD *
FROM addr*.csv (ansi, txt, delimiter is ',', embedded labels, msq)
;
DROP Field DummyField;

Now let’s get rid of those extra fields we don’t want.  First build a mapping list of the fields we want to keep.

MapFieldsToKeep:
 Mapping
 LOAD *, 1 Inline [
 Fieldname
 Address
 Status
 Client
 ]
 ;

I’ll use a loop to DROP fields that are not in our “keep list”.

For idx = NoOfFields('Clients') to 1 step -1
  let vFieldName = FieldName($(idx), 'Clients');
  if not ApplyMap('MapFieldsToKeep', '$(vFieldName)',   0) THEN
    Drop Field [$(vFieldName)] From Clients;
EndIf
Next idx

The final “Clients” table contains only the fields we want, with consistent fieldnames.

Working examples of this code for both  Qlik Sense and QlikView  can be downloaded here.

I hope you find some useful tips in this post. Happy scripting.

-Rob

 

 

Share

Art of the Possible: Dynamic Values

One of my goals in doing presentations and classes on Qlik Sense APIs is to get people to see the “Art of the Possible” — that is, seeing what opportunities may exist in your organization to leverage the power of the associative engine and the Sense APIs, beyond the standard client and hub.

Today I’ll highlight a demo built by my colleague Nick Webster.  This chart actually came out of a class when a student asked “could we…”.

This example uses a standard Qlik Sense table that shows Sales in both USD and Euro.  The twist is that the Euro column is calculated dynamically using an exchange rate fetched from a web service.

http://websy.io/demos/aop1 (select “Live Currency” from menu)

This visualization appears in a web page, but the data and the visualization (optionally we shall see later) are in Qlik Sense.

The Euro column is the sales amount multiplied by a variable representing the Euro rate. The Measure is:

Sum([Sales Amount]) * $(vEurRate)

Every 20 seconds a JavaScript function in the webpage makes a call to a web service to get the current USD/Euro rate.  The new rate is then assigned to the variable with a single API call:

app.variable.setNumValue("vEurRate", eurRate);

The chart will  refresh with the new calculation. Do we have to do anything to get the updated values to display? No! The visualization is automatically refreshed.  The API takes care of all the plumbing to detect the change and update the display.

Does this table viz need to appear in the Qlik Sense app?  It may be created and maintained in the app , in which case it is inserted into the web page with a single line of code:

app.getObject("pageLocation","objectId");

Alternatively, you can create the entire table visualization using JavaScript code in the web page.

You have options for reuse of existing content  and deciding where the code will reside.  For example, leveraging the skills of the Qlik Devs for expression authoring and visualization creation.  Or creating the visualization by using code only for tight integration with web development.  Or splitting the work where the Qlik Team authors Master Measures in the app and the Web Team builds code generated visualizations that utilize those Measures.

On December 3-6 I will be teaching a “Web Development for Qlik Developers” class in Atlanta.  In this four day class we’ll explore the “Art of the Possible” and get you enabled to begin implementing those possibilities as mashups and visualizations using the Qlik Sense APIs.

The class is geared to Qlik Sense Developers who have no prior experience coding web with web technologies.  We’ll start by learning the basics of HTML, CSS and JavaScript as they apply to Sense.  We’ll move on to reusing existing visualizations and create new visualizations from scratch, even using libraries beyond Qlik Sense.  The class is full of hands on exercises and you’ll come away with your work as well as many samples.

Along the way we’ll look at the requirements and options for licensing and deployment, and integration with popular web frameworks like Angular and Bootstrap.

You can register for the class here.  If you have any questions before registering, reach out to me on the Contact Form.

-Rob

Share

Web Dev for Qlik Devs in Atlanta

Here’s an opportunity to fast track your Qlik team in using Qlik Sense APIs to create extensions, mashups, portal integrations and custom content pages that leverage data and visualizations from Qlik Sense.

In this four-day hands-on course you will learn:

  • The fundamentals of HTML/Javascript/CSS as they apply to QS Development and how to get started with popular frameworks and libraries including bootstrap, enigma.js and picasso.js.
  • Creating Visualization Extensions.
  • The differences and use cases for the various QS APIs e.g. Capability, Visualization, Engine.
  • Key QS API concepts such as the generic object model.
  • Connecting to the QIX engine to retrieve existing content or generate aggregations (hypercubes) on the fly.
  • Visualizing data using third party libraries.
  • Using Qlik javascript libraries picasso.js and enigma.js.

Students will come away with example code and completed exercises giving them the confidence to move ahead on their own.

No prior experience with web programming is required as the course will provide an intro to web dev technologies and how they are used in Qlik Sense Web Development.

Even if you don’t have a specific project in mind, I recommend taking this course to understand the power and potential of the QS APIs.  You’ll be surprised and inspired!

The instructor for this course is Rob Wunderlich, a well-known and respected Qlik Consultant and Trainer, Qlik Luminary and publisher of QlikCookbook.com. For more information on the course please contact rob@robwunderlich.com

Cost for the four days is $2600 and includes all course materials and lunch each day. Register at http://qlikviewcookbook.com/registerAtlanta/

Share

Theming the Qlik Sense Script Editor

Summary: In this post I show how to modify the style of the QS Script Editor window.

An astute reader — Johan Roelofsen — of my blog on bookmarklets noticed I had a bookmarklet named “QS Blackboard” and asked if this was used to change the window background color.  Excellent guess, spot on, but there’s more to it.

Changing only the background color can yield a poor result because the text begins to blend into the background.  I’ll want to change the text colors and possibly size as well to yield something like this:

Fortunately QS uses the CodeMirror (CM) editor which provides a theming capability.  Theming the CodeMirror window is done by loading a style sheet and then making an API call to use that style.

The instructions that follow will show how to implement your own custom theme in QS Desktop (I assume it will work in Enterprise as well, just haven’t tried it).

Note that we will not be modifying any of the installed Qlik software files!

  1. In the folder :
    C:\Users\userid\AppData\Local\Programs\Qlik\Sense\Client

    Create a new subfolder (below Client) named “cmtheme”.  This is the folder that will contain your CM style sheets.

  2. Download or build a theme-name.css file in the cmtheme directory.  You can try out various existing themes on this demo page here and download the corresponding css file from the CM repo here.

“Projector1” available here is a theme I’ve built for projecting. If you read the doc or look at the examples, you’ll see the pattern whereby you must define .cm-s-themename selectors in your CSS.

If you start with one of the CM themes, you’ll want to add selectors for QS script specific elements like function, table and field.

.cm-s-projector1   .cm-function {color: #c678dd;}
.cm-s-projector1   .cm-table {color: #d19a66;}
.cm-s-projector1   .cm-field {color: #d19a66;}

All that’s remaining is to  invoke your theme while in the editor.  I use this bookmarklet that first loads the css file and then calls SetOption on the CodeMirror editor instance:

javascript: (function () { 
 var theme = "Projector1";
 $('head').append('<link rel="stylesheet"  href="../resources/cmtheme/' + theme + '.css" type="text/css"/>');
 $('.CodeMirror')[0].CodeMirror.setOption("theme", theme);
 }());

It’s the same bookmarklet code for any theme, I just change the hard-coded theme variable.

To “unapply” your theme and return to QS native, just press F5 to refresh the browser.

Have fun.  And remember when you tinker with something like this the best approach is to not update the vendor (Qlik) software files and instead seek a non-intrusive approach.

-Rob

Hey Rob, I’m a Qlik Dev. This CSS + Javascript stuff looks like a foreign language to me.  How I can upskill to be able to leverage the Qlik Sense APIs including creating Mashups and Extensions?

Easy answer.  Take the  “Web Dev for Qlik Devs” course from Websy.  You can self-study online through the Websy Academy,  take an on-line instructor led course or have one of the Websy team — including Rob — deliver a course at your site to your entire team. 

 

 

Share

Running QS Regression Test in Batch

If you’ve ever seen my presentations on automated testing of Qlik apps, you know I’m a big fan of the free Qlik Scalability Tools  — both QV and QS.

I use the Regression Test feature to automatically validate Qlik app changes before promoting to production.   Regression testing allows us to validate that charts continue to produce correct results after updates are made to the application or platform software.

This graphic provides a brief overview of the regression testing concept.

The Qlik Sense Scalability Tool (QSST) provides a GUI workbench to run and analyze regression tests.  The workbench is a great tool for a developer or QA Analyst to validate an app on an adhoc basis.

What if I want to run the analysis unattended on a schedule?  For example, after every reload to validate that my app is still working correctly and my expected data was loaded.

In this post I’ll demonstrate using a system monitor to run the regression test as an ongoing validation and send an alert if the validation fails.

I won’t go into the details of setting up the Scalability Tool.  You can get that from the product documentation.  If you would like me to do a presentation on automated testing for your team contact me here.

We will need a system monitor that is capable of running commands on a schedule, monitoring the command output and sending alerts or status via something like email or Slack.  You may already have a capable system monitor installed at your site.

NodeGraph is my current favorite Qlik-centric testing tool for ongoing quality monitoring (I’m a partner).  NodeGraph’s Test module allows for testing expression values,  presence of fields and update times.  At the moment NodeGraph does not support running QSST Regression tests, but I expect it will be part of the product by 2018Q4.

For the time being, I’ll demonstrate batch regression testing using Woodstone’s Servers Alive, a low cost but powerful system monitor I’ve used in the past.  You can try Servers Alive for free.

Let’s assume we have used the QSST workbench to author the regression test scenario and have executed the scenario to create a  baseline log.  When using the workbench, we would re-execute the scenario at a later time to create a compare log and then analyze the differences between the logs using the workbench Analysis tool.

QSST provides command line versions of both the scenario executor (“SDKExerciserConsole.exe”) and the regression log analysis (“RegressionAnalyzerConsole.exe”) .  We will need to bit of script to stitch these two operations together and process the output.

I’ve posted a powershell script here that wraps all the necessary operations and exits with an ERRORLEVEL of “1” if validation fails (differences found between compare and baseline logs), or “0” if no differences are found.

  1. Download the RunRegressionTest.ps1 script and place it in a directory named “Regression”.
  2. Create a subdirectory under Regression named “baseline”.
  3. Copy your baseline log file for each app of interest to the baseline directory.   Do not rename the file.
  4. Following the comments in the file, update the first three variables in  RunRegressionTest.ps1 to identify the location of your QSST install and your scenario json file(s).

We can check that the script runs correctly from a powershell command prompt in the “Regression” directory.

.\RunRegressionTest.ps1 "ABC Sales Demo"

where “ABC Sales Demo” is the name of a json file in our scenario directory.  If all goes well, we should see output messages like:

PS C:\QlikSense-Projects\Regression> .\RunRegressionTest.ps1 "ABC Sales Demo"
 Comparing C:\QlikSense-Projects\Regression\temp\results\ABC Sales Demo_localhost_[1-0-1--1]_18072311263304_regression.log
 to baseline: C:\QlikSense-Projects\Regression\baseline\ABC Sales Demo_localhost_[1-0-1--1]_18072216105188_regression.log
 Differences found

Now to implement this in Servers Alive.  In Servers Alive we define things to test as “checks”.  In this case I’ll use the External(errorlevel) check type and provide the command string to run my powershell script.

The full command string is: (no line wrap)

powershell -NoProfile -ExecutionPolicy bypass -File "C:\QlikSense-Projects\Regression\RunRegressionTest.ps1"  "ABC Sales Demo"  "p2"

where “ABC Sales Demo” is the name of my scenario json file.  “p2” is a dummy parameter that works around a windows bug.  Without that extra parm, a blank space is added to the previous parm causing a failure.

On  the Alert tab I’ll specify to send a message to Slack on failure. On the schedule tab I’ll specify when to run this check.

All set up!  When run either manually or on schedule, a failed check — that is, the regression test found unexpected differences — the check will display red on the SA console and I’ll  also receive a Slack message.

 

 

 

 

I’ve just introduced a number of moving parts.  If this is all new to you what I hope you’ll take away is that formal, structured and automated monitoring of your Qlik Applications is possible with relatively low cost and effort.

Want some assistance with planning your testing strategy and implementation?  I offer consulting in planning and implementation of automated testing and monitoring for your Qlik environment. Contact me if you want to chat about your needs and how I can help.

I also typically do an optional lunchtime session on testing at the Masters Summit for Qlik.  Come along to Philadelphia or Johannesburg to talk testing and many many more Qlik topics.

-Rob

 

 

 

Share

QS Document Analyzer 1.3 Supports Server Apps

I just released the V1.3 update to Qlik Sense Document Analyzer.  Two significant features in this release:

  • The ability to analyze applications on the Enterprise Server.  You still install and execute QS Document analyzer on the Desktop,  but you don’t need to move the application to the Desktop.
  • The option to specify a Bookmark that should be applied before calculating objects.  This is particularly useful when an object has a calculation condition.

Another interesting update is that we are now validating all expressions and will flag any expression that has a syntax error.

You can download the latest version from the tools section of this site.

Thanks to Axis Group for their input on this release.  I’m looking forward to an ongoing collaboration.

You can report any issues with QS Document Analyzer in the issue tracker here.

Share

Expanding the Qlik Sense Editor Pane

Summary: In this post I present a non-intrusive bookmarklet to hide the Sections pane in the Qlik Sense Script Editor to provide more real estate for typing script. 

EditNote in the comments below that hiding the pane can be done out of the box using keyboard shortcuts such as Alt-F11.   That reminds me to read the doc! Nevertheless the concept of bookmarklets is useful and this post is bringing in some interesting contributions. 

I sometimes wish for a larger window in the Qlik Sense Script Editor where we type  statements.  This is especially true when I am projecting and I’ve zoomed my browser to make the text legible to the audience, or I’m saddled with a very low resolution.

The UI allows me to hide the Data Connections pane which provides  more space, but I’m still left with the Sections pane consuming 250 pixels on the left which may not serve me at the moment.

Wouldn’t it be great if I could hide/show the Sections pane on demand, like I can do with  Data Connections? Here’s a simple non-intrusive hack that will allow you to do just that.

Paste the following code in a bookmarklet. If you’re not familiar with bookmarklets, they are small bits of javascript code that can be executed from the bookmarks menu.  If you are using Chrome as I do, it’s as simple as pasting the javascript code in the URL property of a bookmark.  Google to learn more about bookmarklets.  If you are using a browser other than Chrome google to see how to create (if possible) bookmarklets in your browser.   Here’s the javascript:

javascript: (function () {
 var size = ($('.scripteditor-left').css("width") == "0px") ? "250px": "0px";
 $('.scripteditor-left').css("width", size);
 $('.scripteditor-stage').css("left", size);
 }());

Apply the bookmark and here is what the editor looks like now:

Apply the bookmark again and the Sections pane reappears.

 

This is an unsupported hack of Qlik Sense.  If something else does not work in the editor, you should refresh the browser — which will completely remove any effects of the bookmark — before suspecting or reporting that Qlik Sense has a defect.

This is  a very clean technique to add behavior because we have not modified any Qlik Sense files.

I file my QS bookmarklets in a Bookmarks Bar folder named “QS” which gives me easy access in a dropdown to mods I’ve created for Sense.

Here’s another bookmarklet I find useful.  This one opens the Qlik Sense script log folder for QS Desktop.  My userid in the path is hardcoded, you would of course update to the correct folder name for your machine.

file:///C:/Users/rob/Documents/Qlik/Sense/Log

Please share if you develop additional useful bookmarklets for Qlik Sense.

-Rob

 

Share

LET, SET, Quotes

Summary: In Qlik script SET is often a better choice than LET, even when the value contains quotes. 

I sometimes see the LET script statement used when SET would be syntactically  easier and more readable.

A brief review:  SET assigns the given parameter as-is to the variable,  LET treats the parameter as an expression and assigns the evaluated result to the variable.

SET x = 1+3;  // x is "1+3"

LET x = 1+3; // x is "4"

I frequently see a variable assignment like this:

LET eSales='sum(Sales)';

eSales stores an expression that will be used later in charts.  It could also be written (simpler in my estimation) as:

SET eSales=sum(Sales);

So far just a matter of style, but the difference becomes clear when we have quotes as part of the string, for example, “Region={‘US’}”.   As LET requires a quoted string,  embedded quotes require some sort of escaping.  In QV10 and earlier, a common way to write this with LET would be:

LET x = 'Region={' & chr(39) & 'US' & '}';

Not real pretty. Many people carry over this style even though QV11 introduced two single quotes to represent an embedded single quote.

LET x = 'Region={''US''}';

Easier to read for sure.  But I think it’s even easier with SET.

SET x = Region={'US'};

That’s it. No special escaping required, just type it as it should be.  What about those quotes? Shouldn’t SET strings be enclosed in quotes?

I find the documentation on SET to be thin, but here is the rule as I understand it.

Single or Double quotes in a SET statement require no special treatment as long as they are balanced (even number of quotes).

SET x = Region={'US'},Product={'Shoe'};  // Valid

SET x = Region={"U*"},Product={'Shoe'}; // Valid

SET x = I won't go;   // Invalid

If the quotes are unbalanced (odd number), then the entire string needs to be enclosed in quotes or brackets.  Use double quotes if we are enclosing single quotes.

SET x = "I won't go";

SET x = [I won't go];

I always favor SET over LET unless I truly want an evaluation.  An exception to this is the string “$(” which will trigger an Dollar Sign Expansion, even in SET.

-Rob

For more on character escaping in Qlik from HIC see https://community.qlik.com/blogs/qlikviewdesignblog/2015/06/08/escape-sequences

Share

Hiding Mashup Objects

I’m really appreciating the Qlik Sense Mashup facility.  In the simplest case, mashups are when you embed Sense charts in a web page.  Super easy with Sense.

Perhaps you’ve created some charts in your app that exist solely to serve the mashup.  What if  you want to display these charts in the mashup but you don’t want them showing in a sheet when the app is viewed via the hub?

How to make these charts available to the mashup but not appear in the “app”?  Simple.   After creating the chart, make it a Master Visualization and then delete it from the sheet.  The mashup can use the Master Viz Id to reference the object.

If you are using the Dev Hub Mashup editor, note that Master Visualizations appear at the top of the “Sheets and Objects” list, before the first Sheet.

 

 

 

 

 

 

 

 

-Rob

Share