All posts by Rob Wunderlich

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 ‘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 'Region={''US''}';

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

SET 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

Spring Holiday Recommendations

Summary: This post has nothing to do with Qlik. It’s a reach out to the community I have built over the years soliciting holiday recommendations.

I have a two week holiday break between the Masters Summit for Qlik in Prague 3 April and Qonnections 23 April.

My wife Linda favors warm so we are heading south.

Through my experience with this site and the greater Qlik Community,  I’ve been  blessed to travel and make  friends all over the world.  So now I’m reaching out to that larger community with a question that has nothing to do to with Qlik or BI.

Should we spend two weeks in Southern Spain or one week in Spain and another in Morocco?

I’m expecting to hear from my Portuguese, Greek and other friends as well 😉

Where would you go with those two weeks in April?

-Rob

 

Share

Get the Numbers Right

I travel a lot for business and personal reasons. I’ve used TripIt (owned by Concur) for several years to plan and manage my trips.

It’s a  great tool, can’t image living without it.

Today TriptIt sent me a lovely graphic email summarizing my 2017 travel.  The design was  a very fashionable  card layout with share-to-social links.

Pretty cool, but for one problem.  It’s not my data.

Yes, those are definitely not my travel stats.

When I teach Qlik application development, a common  question raised by students or other newcomers is: “What’s the most important skill to master?  Scripting?  Expression writing?  SQL? Pre-attentive attributes? Color rules?”

My answer is always the same: “First, get the numbers right.  Second, keep the numbers right”.

It doesn’t matter how it looks or how fast it calculates if the results are wrong.

Join me at the Masters Summit for Qlik in Prague 3-5 April where in addition to a full range of Qlik Dev topics, we’ll be discussing automating quality.  I’ll be demonstrating tools to automate dashboard validation and continuous quality monitoring.

-Rob

BTW I emailed the TripIt folks about the error, they’ve promised to look into it.  

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

QViewer Acquisition

I’m pleased to announce that I’ve acquired the excellent QViewer tool from Dmitry Gudkov effective January 1.  I’ll be marketing, developing and supporting QViewer going forward.

QViewer has a stellar reputation as a must have tool for Qlik Developers.  I’m pleased to be taking over such a fine product.

This acquisition is one step in my plan to focus more on software development during 2018.  Stay tuned for other announcements during the year.

Existing licensed QViewer customers should email future support requests to support@panalyticsinc.com.

Happy New Year!

-Rob

Share

DevTool Extension for Qlik Sense

One of the benefits of attending the Masters Summit for Qlik is networking and the things you can learn from discussions with peers.

At the Boston Summit, I was fortunate to meet Erik Wetterberg, formerly of Qlik R&D and known to me as the author of the qsVariable and DevTool extensions.  I’ve been a fan of the DevTool extension for some time and had a nice chat with Erik about potential enhancements to DevTool.   Since the summit Erik has made some updates and accepted new function I’ve added to the tool.

DevTool provides some functions that are useful during Qlik Sense app development and debugging.  Download the DevTool zip from the dist folder here https://github.com/erikwett/DevTool and install as you would any other extension.

Add  the extension to any sheet and it will create an Action Button (AB) to the lower right corner of the sheet.

You don’t have to leave the extension in the app, the AB will remain until you close the app.  I usually don’t leave it in the app, I just add it and then remove by double clicking DevTool in the assets panel and follow that with a Ctrl-z undo.

You can leave it in the app if you wish.  There are some considerations discussed below for leaving it in a published app.

Clicking the AB will toggle a tooltip above each sheet object that initially displays the objectid.  The objectid is useful when building mashups or reviewing diagnostic data.   As selections are made and the objects are recalculated, the tooltip will add a line showing the time in milliseconds for the current calculation as well as the max calculation time for this object.

 

Click the “properties” button on the tooltip and this object’s json formatted properties will display in a popup.  Use the copy-to-clipboard button to copy all or selected property lines to the clipboard.

Right-Click the AB to open a context menu that provides for:

  • Exporting script to a text file.
  • Importing (replacing) script from a text file.
  • Exporting variables in script or json format.

The extension works equally well in desktop or server.  Some things to think about before including it in a published app.

  • Users normally can’t see the load script and variables in a published app, but they can with this extension.
  • Although the script import works without error in a published app, the change to script is not saved so effectively nothing has been updated.

For myself, I’ve been thinking of this as a tool to use during development only and have not been including it in published apps.  I’d be curious to hear if someone has made a different decision.

The extension buttons use a google font. I note that many servers are blocked from the internet. If this is your case, you will see red dots for the buttons instead of proper symbols.  The buttons are still fully functional.

Maybe I’ll see you at the Masters Summit in Prague  where you might find your own interesting collaboration.

-Rob

Share

Rob Joins Websy.io

I’m pleased to announce that I’m joining forces with the team at websy.io!  I look forward to bringing the websy training  offerings to locations in the US as an Instructor and event organizer.

I’m also excited about the opportunities to bring websys’s recognized expertise to Qlik Sense Integration and Visualization projects throughout the world.

If you would like to inquire about scheduling a training at your site or would like to engage the experts at websys.io in your project, please contact us.

-Rob

 

Share

Mass Editing of QVW Script

Summary: In this post I describe a process using freely available tools to apply changes to all scripts in a set of QVW files.

So you have a lot of QVWs. And now you are asked to identify and make updates to all scripts to support changes such as:

  • A change in QVD or other file naming.
  • Changes in file paths due to a server move or directory restructuring.
  • Updating file paths to use variables.

If you are super cool, all those items are represented by variables, changes are handled by updating a single include file and you can relax and stop  reading this post. For the rest of us, read on.

You can scan and search all your script using my Script Repository tool.  That will allow to identify where changes are required, but do you have time to edit every QVW and make the changes?  Easy enough for a few, but what about when you have dozens?

QlikView Desktop has a useful facility we can leverage for mass changes; the “-prj” folder.  If a folder named qvwname-prj  (case sensitive) exists in the same folder as the QVW, when the QVW is saved, QV Desktop will write a set of text files representing the structure of the QVW to the -prj folder.  One of those files is “LoadScript.txt” which contains the load script.

When QV Desktop opens a file, it checks for the existence of a companion -prj.  If found, it populates the QVW with the contents of the files in the -prj.  If we change one of those files, for example “LoadScript.txt”,  that change will be inherited by the QVW.

Let’s walk thorough a scenario where we can utilize this feature to update the scripts of an entire set of QVWs.  I’ll utilize free tools that will make the process easier.

My sample problem is this: I have inconsistent QVD naming conventions. We’ve decided that “DimCustomer.qvd” shall henceforth be known simply as “Customer.qvd”. I’ll need to update the script that generates the QVD as well as all readers of the QVD.

I will accomplish this update in four steps:

  1. Create -prj folders for all QVWs.
  2. In the “LoadScript.txt” files replace “DimCustomer.qvd” with “Customer.qvd”.
  3. Rebuild the QVWs with the updated -prj.
  4. (Optional) Delete the -prj files.

The sample I’ll use for this post is relatively small to keep the demo simple.  But I’ve used this technique to process hundreds of QVWs at a time incorporating several different script edits.

I have a directory of QVWs that looks like this:

 

 

In the SubFolder “Loaders”, there are additional QVWs.

 

 

I’ll need a -prj folder for each QVW. I  can create the -prj manually, but this is where I can leverage the PrjTool to make life easier.  You can download the PrjTool from the Tools section of this site.  (Note: If you received a copy of PrjTool from the Masters Summit, please download this newer version as it contains important updates.)

PrjTool requires a Directory as input and the selection of one of  three functions:

  • BuildPrj: For all QVW files found in the specified Directory, create a -prj folder.  This includes opening and saving the QVW to populate the -prj.
  • CreateFromPrj: For all -prj folders found in the specified Directory, open and save the QVW to update the QVW with contents of the -prj.  If no QVW exists, a new one will be created.
  • DeletePrj: Delete all -prj folders found in the  specified Directory.

I’ll start by specifying the Directory that holds our QVWs and selecting the BuildPrj function.  Press the Execute button and the script will launch. The execution may take some time as each QVW has to be opened and saved. Good time to go for a coffee.

When the execution completes the log window will be filled with messages listing the -prj folders created by the tool.

 

If we examine the directory again we will see the new -prj folders.

 

Our next task is to edit the LoadScript.txt files. We can use any editor capable of searching and replacing across multiple files.  For this demo I will use the free NotePad++ editor.   From the NotePad++ menu, launch “Search” , “Find in File”.  In the search dialog I specify the Directory  and  the search and replace strings. I’ll also limit the search to the LoadScript.txt files.

 

After pressing “Find All”,  I’ll get a list of search results.  I can double click any of the results to open the file for further examination.

 

When I’m satisfied that I’m going to make the correct updates, I again launch “Find in Files” and press “Replace in Files”  to perform the update.

Now I’ll use the PrjTool again to update the QVWs with the updated -prj files.  I run the tool again, this time selecting the “CreateFromPrj” function.  Again, if you have a lot of large QVWs, be patient while the tool runs.  The resulting log messages will inform me of the updates.

We’re done!  All QVWs now contain the updated load script.  Optionally we can run the tool again with the “DeletePrj” function to delete the generated -prj folders.

You should always perform this kind of mass update activity on copies of QVWs and audit the results.  Also, never use -prj folders in production.  Server reloads do not recognize -prj folders.

-Rob

 

Share

Quick Sense App in a Page

Summary: Learn an easy method to deliver a Qlik Sense app as an embedded web page. Yes, the spelling of this post’s title is intentional 😉

I just came off a week of Qlik Sense API & Integration training with Nick Webster of Websy.  I’ve been nothing short of impressed with the integration and reuse options of Qlik Sense.

I was intrigued today by a question on Qlik Community asking if there was a way to disallow the application Edit mode in Qlik Sense Desktop.  The poster wanted his students to just “use” an app and not poke around in the design until a later time.  Kind of like the “distraction free” mode  in text editors.

The challenge I gave myself was how quickly could I knock out a solution to this problem?  I’ll detail my solution below and I’ll tell you it is taking me longer to write this blog post than it took to build the solution.

Rather than working at disabling or removing function I didn’t want, I approached the problem as including only what I wanted. My first thought was to create a mashup that represented the entire application.  Then I struck on an even easier approach — use the Qlik Sense “Single Integration API” with a bit of Bootstrap.   The Single Integration API does not require writing any code.  It’s just a URL that displays a single Visualization or complete Sheet.  You can create URLs and experiment with options in the Dev Hub Single Configuration Tool.

Bootstrap is a popular web design toolkit that makes it easier to create responsive and interactive web pages.  Thanks to Nick’s class I understood how Qlik utilizes bootstrap.

A basic URL to display a sheet looks like this:

http://server/single/?appid=myappid&sheet=sheetId&opt=options

Appid is a qvf filename on QS Desktop, or the GUID if using QS Server.  Options allow you to control  things like allowing selections.  A complete list is in the API doc.

With 50 lines of html I created a web page that delivers the full associative experience across seven sheets, including Current selections, Smart search and the Selections tool.

Here’s a screenshot showing my sheet navigation buttons across the top and the Sheet content, including Current selections, immediately below the buttons.

The html file is available for download here.  I used the “Sales Discovery” sample app but it is easy to adapt the file to any application.

There is a single iframe nested in a responsive Bootstrap container.  The buttons simply change the src attibute of the iframe to load a specific sheet .

If you want to try the file yourself you  only need to change the “var appid=” string to point to the app path on your machine.  No web server is required, just double click the html file to launch.  Your server or QS Desktop must be active.  Yes, this file works as is with QS Server.

Nick and I will be showcasing a variety of QS integrations at the Masters Summit for Qlik in Boston Oct 23-25.   Nick will also be presenting  a half day “Qlik Sense Integration” session at the summit that will teach you how to create a basic web page with bootstrap and explore more advanced QS integration options.

-Rob

 

Share

QV12 Variables with Alt State Fixed

Summary:  QV11 contained an inconsistency in how variables with equal signs were evaluated when using Alternate States.  This has been fixed in QV12.10.  Read on if you want the details. 

QlikView V12.10 includes an important fix to variable evaluation when using alternate states. A quote from the Release Notes:

In QV11.20 the variable was expanded in the first state encountered and this resulted in a random behavior when more than one alternate state was being used. Whereas in version 12 and up, the variable always belongs to a specific alternate state and this results in different behavior.

The random behavior described in QV11.20 has generated several interesting posts to QlikCommunity with responses of “I can reproduce” / “I can’t reproduce” and few clear answers.

I find the problem confusing and interesting enough to warrant an explanation and example beyond the Release Note.

What I am describing in this post only affects variables with a leading “=” in the definition, e.g. “=Sum(Sales)”.  These variables are calculated once in the context of the entire document. They are not calculated per row in a chart.

Let’s consider a variable named “vSumX” with a definition of “=Sum(X)”.  The expression simply sums all selected values of X.   Suppose we have two States in our document — “Default” and “State1”.  There could be two different selections for “X”.  Which set of X should the variable sum?

If we consider the variable definition in isolation, the answer is “Default set”  as there is no set identifier in the expression.  But what if the variable is referenced in an object in State1.  Should the State1 values of X be used?

No matter what you think the rules should be, here’s what was happening in QV11.20.  The variable was expanded (evaluated) in the first state encountered.  First state encountered means first state in the calculation chain, not something the developer directly controls.

Let’s look at some examples.   I’ve created a sample app (you can download here) with three States — Default,  State1 and State2.  The variable “vSumX” is defined as “=Sum(X)”.

With all objects on sheet in the Default state, selections in X would yield results like this.  (Note “$” indicates default state).

The first text box contains the expression “Sum(X)”.  The second text object contains the reference to variable vSumX.  The two values are what we might expect, summing the selected values of X in this state.

Let’s switch to a sheet that contains objects in the state named “State1”.

No selections in X and the first text object shows the expected result.  The second object shows the value of vSumX as previously calculated from the default state.  If we make selections on this State1 sheet, that will cause vSumX to be recalculated and both State1 and the Default sheet will reflect the State 1 number.  Is that correct?  Is it useful?  It’s at least consistent and comprehensible.

My next example is where the aforementioned “random” comes into play.  Let’s put objects from three states on the same sheet.

I’ve selected some values in the Default state of X and the results are what I might expect.  The value of vSumX is calculated from my last selections and the variable value is consistent across objects — there is only ever one value for a variable at a given point in time.

Now I select some X values in State1 and expect to see a new value (19) for vSumX.  But no change! The variable was expanded (evaluated) in the first state encountered which happened to be Default ($).

Now I select some X values in State2.  If the vSumX calc used my last selection I would expect to see 7.  But no, I see 19.  The State1 values were used.  If I repeat the exercise, it may use a different state to calc vSumX.  If you test you may get different results.  In this last example, State1 was used because it was the first state encountered in the calculation chain.  The order is not consistent.  It will be influenced by factors such as number of available processors and the order in which the objects were created.

Now that we’ve established that QV11,20 is broken in this regard, how was it fixed in QV12.10?  Simple.  QV12 uses  set identifiers as specified in the expression, without inheritance.

=Sum(X)

will use the Default State as there is no identifier.   If you want to Sum from a specific state, use it in the expression:

=Sum({State1} X)

Variables do not belong to any State.  Aggregation functions used in a variable may specify a Statename, just as chart expression do.  The difference is that the absence of a set identifier in a chart expression means “inherit the state from the containing object”.  In a “=” variable, no set identifier means “use the default state”.

A reminder that end of standard support for QV11 comes on Dec 8, 2017.  If you haven’t yet upgraded to QV12.10, I encourage you to do so.  Download my  QV12 Upgrade Considerations Doc as part of your planning process.  Feel free to contact me if you want some assistance with your upgrade.

-Rob

Update:  Qlik has extended support for QV11.20 to March 31, 2018. 

Share