All posts by Rob Wunderlich

Masters Summit for Qlik Cloud

Masters Summit New Qlik Cloud Track!

The Masters Summit Team is developing all new courses for 2024. The new content is focused on:

  • Qlik Cloud (perfect for clients who are on or moving to cloud already, or evaluating a migration).
  • New capabilities in the platform, like AutoML, Automation, etc.

Before we get too far down the road, we want your input to gauge interest.

We are opening pre-registration for our 2024 conferences:

Vienna, Austria – October 02-04 Pre-register for Vienna now

Philadelphia, USA – November 04-06 Pre-register for Philadelphia now

If you or one of colleagues would be interested in attending one of these Summits, and believe you can get approval for the expense we would ask you to kindly pre-register now.

We will not be taking any payment at this time and you should not make any travel arrangements.

Why pre-register?

If you pre-register on or before January 15, 2024, you can attend the event for a specially-discounted price of $1800 (that’s a 25% discount off our usual early-bird price)

This is your opportunity to help shape the future of Masters Summit events and attend with a deeply-discounted price. If we determine enough of you are interested in returning, based on pre-registration, we will move forward with our 2024 plans

See the new Sessions below.

Managing your tenant

  • Setting up development and promotion flows
  • Approaches for managing data.
  • Monitoring your tenant

Migrating to Qlik cloud

  • Planning your migration
  • Common pitfalls
  • Available tools, and how to use them.

Performance Tuning and Capacity Management

  • Best practices for performance and data scalability with the Qlik capacity licensing in mind.
  • Review opportunities in data modelling and visualizations to improve performance.
  • Review available tools for monitoring performance.

Real-world AutoML Applications

  • Understand machine learning predictive models and what business problems they can tackle.
  • Profiling data for ML, basic statistics, feature engineering, avoiding common pitfalls.
  • Evaluating and explaining your models using AutoML results
  • Deploying and monitoring your model

Enterprise Reporting

  • Building reports from exiting sheets or objects
  • Output presentation quality reports in various formats including annotations.
  •  Automating the distribution of reports to a variety of delivery channels.
  • Review existing report connector blocks.
  • Discover the self-serve options for subscription and alert-based reporting.

Qlik Automations

  • Automating Analytics Operations and Automation
  • Dynamic Actions and Business Process Integration
  • Going beyond: Qlik-CLI and the Qlik APIs

Qlik Cloud Integration

  • Understand what APIs are available, the necessary skill sets required to develop with them and how to correctly structure your project architecture.
  • See the art of the possible with a mix of real-world solutions and innovative demos.
  • Discover useful tools to help speed up development and reduce costs.
  • Get tips and tricks for the dark art of authorization and authentication in both client and server implementations.

We hope to see you in 2024.

Sincerely, The Masters Summit Team.

Share

Using Attributes in QVD Files

Here’s a fun Friday post.

As I was updating my incremental load materials for the upcoming October/November Masters Summit for Qlik, I ran across an old note about creating “Attributes” in QVDs using Tags. Due to a bug in QVD generation I couldn’t implement the idea at the time. But the bug is long since fixed so here I present the general idea of “QVD Attributes” (my name, not an official Qlik thing).

We can assign Tags to Qlik Tables using the script TAG statement:

Tag TABLE TranData with 'MaxId=$(vMaxId)';
Tag TABLE TranData with 'Source=Salesforce';

If the table “TranData” is subsequently written to a QVD using the STORE statement, the tag will be included in the QVD file.

We can read tags directly from a QVD with script:

LOAD String%Table as Tag
FROM [lib://mylib/TranData.qvd]
(XmlSimple, table is [QvdTableHeader/TableTags/String]);

Note that we are reading only the XML header from the beginning of the file. This is very fast.

If we use a structured tag format like “key=value” we can create a generalized “Attribute” store in a QVD that can tell us something about the contents of that QVD. That could be useful when we want to use the QVD in a subsequent script.

How do we retrieve these tags in a useful and consistent way? I think loading them as variables would be a good approach. We can create a reusable subroutine to load all “key=value” tags from a QVD and generate variables named “Tag:key”. We then call the Sub, using the path to any QVD:

Call LoadTags('lib://QVDFiles/TranData.qvd');

We might utilize the new variables in something like an incremental Load.

SQL SELECT * From Trans Where Id > $(Tag:MaxId);

Or as a control to conditionally execute a script block.

If '$(Tag:Source)' = 'Salesforce' Then
// Use the Salesforce Load statement
EndIf

What is all this stuff? Subroutines? Preceding Load? Incremental Load? Tags? Join me at the Masters Summit for Qlik in Dublin or Orlando where we will teach you all about these topics and make you a scripting Master.

At the bottom of this post is a complete sample including the subroutine. You only need to change the “QVDFiles” to your lib to run this on your system.

-Rob

// Subroutine to Load Tagname=Value pairs from a QVD as "Tag:Tagname" Variables.
Sub LoadTags(_path)
    _TempTags:
    Load
        SubField(Tag, '=', 1) as TagName,
        SubField(Tag, '=', 2) as TagValue 
    ;    
    LOAD String%Table as Tag
    FROM [$(_path)] 
    (XmlSimple, table is [QvdTableHeader/TableTags/String])
    Where String%Table like '*=*';
    
    For _i = 0 to NoOfRows('_TempTags') - 1
        Let _vname = 'Tag:' & Peek('TagName', $(_i), '_TempTags');
        Let [$(_vname)] = Peek('TagValue', $(_i), '_TempTags');
    Next _i
    Set _vname=;
    Set _i=;
    Drop Table _TempTags;
End Sub
// END of Subroutine


// Generate some sample data
TranData:
LOAD  
	RecNo()*2 as Id,
	Rand() as Amount,
    'A' as Customer
AutoGenerate 6;

// Something we may want to record from the data is the max Id value, for later incremental load. 
TempId:
LOAD Max(Id) as MaxId Resident TranData;
Let vMaxId = Peek('MaxId');
Drop Table TempId;

// We can tag with $(variable) values or literal values. 
Tag TABLE TranData with 'MaxId=$(vMaxId)';
Tag TABLE TranData with 'Source=Salesforce';
// Some other tag that is not part of our name=value scheme.
Tag TABLE TranData with "ABC";
// STORE and DROP the QVD
Store TranData Into [lib://QVDFiles/TranData.qvd] (qvd);
Drop Table TranData;


// Call our subroutine to load tags as variables, from the disk QVD.
Call LoadTags('lib://QVDFiles/TranData.qvd');

// Demonstrate how we might use these tag values.
// In a SQL Where clause.
SET select = SQL SELECT * From Trans Where Id > $(Tag:MaxId);
Trace $(select);

// In a conditional to select a block of script.
If '$(Tag:Source)' = 'Salesforce' Then
	Trace Going to use the Salesforce load statement;
EndIf
Share

Guest Speaker for Dublin Masters Summit

I’m pleased to announce that Brian Booden will join us at the October Masters Summit for Qlik in Dublin, Ireland. Brian is a seven (!) time Qlik Luminary / Partner Ambassador, co-host of The Data Mix, and a well known Qlik and Analytics expert.

Brian will share some inside tips on using Qlik Application Automation and I expect a few interesting tricks. I’m hoping he shows off the capabilities of his WhatsApp for Qlik integration!

Masters Summit for Qlik is the premier Qlik Sense & QlikView advanced education event. In three packed days of lectures and hands on exercises, you’ll learn from experts and take your Qlik Dev skills to the next level in data modeling, scripting, expressions and visualization. We’ll also cover performance tuning and Qlik API Integration for beginners. You’ll come away with a wealth of new knowledge, code samples and documentation.

Find out what over 1500 of your colleagues have learned already.

Share

Creating Time Groups in Your Data Model

I frequently create Date groups in my Qlik apps to facilitate selections like “Yesterday” or “Last Week”. Some years ago I published a pattern for creating Date groups in the Recipes section of QlikCookbook.com and it’s been a very popular download. I’ve responded to a few requests on Qlik Community looking to do the same thing for Time groups, so I decided to publish a recipe for Time Grouping as well.

The recipe demonstrates selecting ranges such as “Day Shift”.

Selecting by Time Group

How do we create ranges that map to our data? In the script, create a Range table that defines range Names, Start and End times.

Ranges:
LOAD
Range as Range,
Time#(RangeStart, 'hh:mm:ss') as RangeStart,
Time#(RangeEnd, 'hh:mm:ss') as RangeEnd
INLINE [
Range, RangeStart, RangeEnd
Midnight Shift, 00:00:00, 05:00:00
Early Morning, 05:00:01, 09:00:00
Daylight, 06:00:00, 17:59:59
Day Shift, 09:00:01, 17:00:00
Early Evening, 17:00:01, 20:00:00
Evening, 20:00:01, 23:59:59
12am-6am, 00:00:00, 05:59:59
6am-12pm, 06:00:00, 11:59:59
12pm-6pm, 12:00:00, 17:59:59
6pm-12am, 18:00:00, 23:59:59
];

Then use IntervalMatch to link the time field in our data (EventTime in this example) with the Range field.

JOIN (Ranges) IntervalMatch (EventTime) LOAD RangeStart, RangeEnd RESIDENT Ranges;
// No longer need RangeStart, RangeEnd, but may keep them for documentation or debugging.
DROP FIELDS RangeStart, RangeEnd;

There are additional notes provided in the downloadable example, but that’s it for the basic pattern. You can download the example from here Time Grouping

You may have noticed I used Time#() to read the text time values. That makes it easy to write the range values as readable hh:mm:ss.

If you are generating the ranges using another technique like Interval#() or division, be mindful of rounding. A Qlik time value is represented numerically by the fraction of 1, a whole day. 0.5 is 12:00:00 PM, halfway through the day.

There are four methods I’m aware of to generate a Qlik time. For example to generate the numeric time value corresponding to 12:00:05 AM:

Time#('12:00:05', 'hh:mm:ss')
Interval#('12:00:05', 'hh:mm:ss')
MakeTime(0,0,5)
5/86400

All four will generate a floating point number to represent the time. Will they produce exactly the same result? Results that would pass an equality test?

Time#() = Interval#() = MakeTime() = x/y?

For some inputs they are equivalent. For a significant number of inputs the odd man out is Interval#(). Given the 86,400 second values in a day, Interval#() will not match the others 36% of the time. Try sharing that interesting fact with your spouse at the breakfast table.

Join me at the Masters Summit for Qlik in Dublin or Orlando this fall to learn advanced scripting techniques for Qlik as well as inside tips. Our team of expert consultants will cover data modeling, set analysis, visualization techniques and Qlik integration as well as answer your “how do I” questions. Register now or get more information at the Masters Summit for Qlik website.

Happy Scripting
-Rob

Share

QSDA Pro 2.5 Brings Extended Expression Validation

Summary: Set Modifier field names and Set Identifiers are not validated by Qlik syntax check. QSDA Pro 2.5 validates everything and raises an “Unrecognized Name” flag for invalid names.

QSDA Pro syntax checks all expressions in a Qlik App using the Qlik syntax checker. You’re familiar with the Qlik syntax checker. It’s the message at the bottom of the expression editor that tells you your expression is “OK”.

The syntax checker is also good at telling you when the expression has an error, although it’s not always specific about the problem.

QSDA Pro, using the Qlik API version of syntax checker, tells you what is wrong with the expression:

The Qlik syntax checker has a significant limitation. It does not validate field names used in sets. Whether used in the API or the editor, the syntax checker will not raise an error if the field “EmpPurchase” does not exist in the data model.

This lack of validation can be a serious problem if “EmpPurchase” is removed or renamed in the data model. The expression will still return a number but the exclusion of employee purchases will no longer be applied. That could be a very subtle but important fail.

Recognizing this limitation and it’s potential impact, QSDA Pro 2.5 validates all names used in sets and will raise a new Quality flag, “Unrecognized Name”, if the name is invalid.

Another place this validation is extremely useful is Chart Filters in Qlik SaaS. The chart will cheerily display that filter “Expression2 > 1” is being applied. Even though field “Expression2” no longer exists in the data model.

But QSDA knows.

Ever use a bookmark as a set identifier? And then the bookmark got deleted or renamed?

I’ve used simple examples to demonstrate, but where this validation really shines is in longer expressions where it may be easier to overlook that something has gone wrong.

Yes, you need QSDA Pro. We all need QSDA Pro.

Learn more about QSDA Pro and download your own copy at https://easyqlik.com/qsda/

-Rob

What does QSDA stand for? Qlik Sense Document Analyzer. The combination of data model and sheets we know as an “App” in Qlik Sense was called a “Document” in QlikView. When I first created this tool in 2009 to help maintain QlikView, I called it “Document Analyzer”. When it came time to create a similar tool for Qlik Sense, I stuck with the “DA” name as the function and usefulness of “Document Analyzer” was well established in the Qlik community.

Share

Motio Acquires QSDA Pro

I’m pleased to announce that I’ve joined forces with Motio, Inc, producers of the great DevOps tools Soterre and Gitoqlok. Motio has acquired my QSDA Pro product and I’m excited to combine our tools to provide a comprehensive platform for professional Qlik App development in Qlik Sense and Qlik SaaS. You can read the announcement here.

I will continue to lead the development of QSDA Pro, enhancing the product and bringing new superpowers to my customers in the integration with Soterre and Gitoqlok.

The QSDA Pro customer base is growing fast! To date hundreds of organizations have purchased QSDA Pro and are realizing the full benefits of the tool. More are joining every day and I’m excited to add Motio’s dedicated product support and admin teams to handle this growth.

You can continue to learn more and purchase QSDA Pro online .

Share

Gitoqlok for Qlik Sense Version Control

Gitoqlok is a free chrome plugin that allows you to use a git repository such as GitHub, GitLab, Azure DevOps, etc, to manage all the pieces of Qlik Sense app — Measures, Sheets, Charts, Load script and more.

The plugin operates seamlessly within the Qlik Sense authoring experience to provide the power of fine grained version control you expect with git. You’ll see a list of changes and commit those changes as you would expect in any software development project. Of course there is collision detection, branching and merging and all the goodness you expect from git.

Because the Gitoqlok team understands the visual nature of Qlik development, you can view diffs in a visual format. A slider lets you pick a commit for compare.

You can also use the same slider in a non-diff “time machine” mode to see how the application sheets looked at any point in time.

Gitoqlok comes with a lot of very useful Qlik dev features, such as the ability to import a chart or script snippet from app to another. Or deploy an entire app from one server to another!

Most Gitoqlok features are free to use. Premium features and support are available in a reasonably priced subscription.

If you haven’t tried Gitoqlok I recommend you give it a spin. You can install the plugin from the Chrome Store. Learn more about Gitoqlok including some great intro videos.

If you are attending QlikWorld next week Drop by the Motio booth (#315) to see more. Motio is also presenting two breakout sessions showcasing Gitoqlok, including their integration with my QSDA Pro tool.

-Rob

Share

Qlik App Performance Tuning — March 29

My Masters Summit for Qlik colleague Oleg Troyansky will be presenting his always valuable “Performance Tuning” course on March 29 as part of our “Masters Summit at Home” online series.

Oleg will do a deep dive into the causes and corrections for Qlik App performance issues. He’ll explain the why of good and bad performance while explaining important Qlik Engine concepts and demonstrating best practices. I highly recommend this course.

Oleg will demonstrate available performance tools including my favorite — QSDA Pro. As part of the class each student will receive a trial license key for QSDA and I’ll be available before and after class to help you get QSDA installed and configured.

Also coming up in the “Masters Summit at Home” series:

Effective Visualizations” with Bill Lay on March 15. Bill will teach you how to effectively “tell the story” using Qlik visualizations in a series of interesting and challenging scenarios. Bill is an engaging and thoughtful presenter. I always come away from Bill’s lectures with fresh ideas and useful tools.

Are you Qlik Integration / API curious? Want to understand the potential of combining the web and the Qlik platform? Join Nick Webster on April 12 for “Qlik Sense Integration“. Nick begins by showcasing Qlik embedded in intriguing and useful ways. He then moves on to teach the basics of web technologies HTML, CSS and Javascript, which you will then use to construct a fully functional, interactive Qlik web application in class. No web programming experience necessary!

Share

Mind the Concat() sort-weight

Summary: While looking into long expressions I noticed that the optional sort-weight argument has an impact on the distinctness of Concat(distinct…). Incorrect use of sort-weight can generate bloated expressions containing redundant code.

In my work tuning Qlik Apps I sometimes encounter very long expressions. An expression many thousands of characters long can be difficult to debug or comprehend the expression goal. To help in working with long expressions I’ve added an Expression histogram and an Expression Decoder feature to my QSDA Pro product. (These features are currently in beta, generally available in early Feb).

I’ve noted expressions of length greater than 50k across apps from different customers. What did these expressions have in common that made them so large?

  • They used the Concat() function in $() to dynamically generate a part of the expression.
  • They used the optional sort-weight argument of Concat() incorrectly.
  • They were much bigger than necessary — sometimes 100x — but the expanded expression worked as intended.

In the process of reviewing the expressions I learned something surprising. As a reminder here’s the syntax of the Concat function:

Concat({[SetExpression] [DISTINCT] [TOTAL []]} string[, delimiter[, sort_weight]])

We use the DISTINCT keyword to return the unique set of values from the string argument (usually a field). The Qlik documentation for DISTINCT says:

If the word DISTINCT occurs before the function arguments, duplicates resulting from the evaluation of the function arguments are disregarded.

https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/StringAggregationFunctions/concat.htm

This means that the set of distinct values is the combinations of string and sort_weight (if used). Let me demonstrate with an example. Here’s a sample data table.

For the expression: Concat(Dim, ',') we receive output "a,b,c,c,c“.

Adding the DISTINCT keyword: Concat(DISTINCT Dim, ',') we now get “a,b,c“.

Adding a non-distinct sort-weight argument: Concat(DISTINCT Dim, ',', RecId) we now get "a,b,c,c,c” again. More output than I expected. It’s a distinct list of the combinations of Dim and RecId.

Adding a distinct sort-weight argument: Concat(DISTINCT Dim, ',', Weight) we now get "a,b,c“.

How about if we used an unlinked data island field for sort-weight? The Island field has two values.

Concat(DISTINCT Dim, ',', IslandField) returns "a,b,c,a,b,c“. Item count is the product of Dim * IslandField values. Remember this for later.

Ok, this is all very interesting but the behavior is super obvious and I would notice it if it came up in my App. What’s this got to do with ginormous expressions?

Developers sometimes use Concat along with Dollar Sign Expansion (DSE) to generate dynamic expression fragments. For example to ignore all fields from several tables in a set modifier:

Sum ({<
$(='[' & concat({<$Table={'Table1', 'Table2', 'Table3'}>}$Field,']=,[') & ']=')
>} Value)

Sometimes $(=Concat(...)) is used to build the list inside a Pick() or Match(). These type of expressions frequently have awkward syntax including lots of “& chr(39) &” type stuff. Inevitably the expression gets built by copying and modifying an expression from elsewhere in the App. An expression that contains a sort-weight. A sort-weight that doesn’t get removed. It may be an island field or a field that has a many to one relationship. The result is an expanded expression that works but is larger than it needs to be. No one notices (unless they use QSDA Pro) because it’s the expanded expression.

As a simple example, suppose the “ignore filter” expression above was supposed to generate something like "Quarter=,Month=,Year=“. If I inadvertently use a sort-weight field that has 100 distinct values the result will be repeated 100 times. The expression would still work but it would be 100x larger than necessary.

I recently found an example where Concat was used to generate an If() function from data (very clever) that should have had 15 branches. But an unrelated sort-weight field of 95 values resulted in 1425 branches! It “worked” but did a lot of unnecessary calculation.

If you are a solo developer or working in a small team you may never encounter this issue. But if you are a consultant or maintaining legacy Apps you may stumble across it. I’ve been playing with ways to flag this condition in QSDA Pro. QSDA already flags data island expressions . I’m testing creating a new flag specifically for Concat().

My colleague Oleg Troyansky uses QSDA in his Performance Tuning session at the Masters Summit for Qlik. Live events will return in Fall 2023 but in meantime you can attend individual workshops on-line during February through April. More information and schedule here.

Happy Qliking
-Rob

Share

Outer Sets Bring New Reusability

Summary: I demonstrate how outer sets in Qlik expressions can be used to extend the reusability of Master Measures.

The new outer sets feature in Qlik brings new opportunities for Master Measure reuse in Qlik Sense and QlikView. See this post by Henric Cronström for an explanation of the new feature.

Without outer sets if we wanted to inject set analysis into a measure, we had to copy the expression text and edit. Consequently we are no longer linked to the master item and have lost the maintenance and quality benefits of being linked.

Let’s look at an example where outer sets can improve reusability. In our sample scenario, the business rule for “Sales” is sales receipts minus returns and excluding employee purchases. Our Sales master measure might look like this:

Note that we had to repeat the same set inside each aggregation. We can simplify the expression by using outer set syntax and defining the set once.

This does nothing for reusability, but I believe it improves maintainability. The outer parentheses are not strictly necessary in this case, but they do make it clearer that the set applies to the entire expression.

Continuing on we define a COGS measure:

Note that we could have used the outer set syntax, but I don’t see any advantage to that here.

With the ability to use Measure names in expressions, available in Feb 21 release, let’s define a Margin measure.

Now we can drag the Sales and Margin measure into a KPI.

Now we want to do Sales and Margin for the current year only. Our data requires that we inject a new set. This is where outer sets really shine. The Current Year Sales measure using an outer set:

The Current Year Margin measure:

Note that we have maintained our linkage to the Sales and Margin master measures!

In review, here are all five Measures taking advantage of the outer sets syntax.

I’m happy to see outer sets available in the product. I look forward to the day when sets may be master items!

-Rob

Share