Category Archives: Productivity

General, Productivity, Tools

Motio Acquires QSDA Pro

1 Comment

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
Productivity, Qlik Sense, Tools

Gitoqlok for Qlik Sense Version Control

Leave a comment

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
Productivity, Qlik Sense

The New Qlik Simplified Authoring Mode

Leave a comment

Qlik has implemented a new “Simplified Authoring” experience in the SaaS (Cloud) environment. It’s available as an optional preview now and will be implemented for all SaaS tenants in mid-August.

I won’t describe the new features as they are well described here. If you haven’t already, I recommend you read the post and then come back here for my comments.

I think this is really great work and a significant improvement to the chart authoring experience. It not only makes it easier for newbies to create charts, I prefer using this interface in most cases. What do I like about it?

Moving the data panel to the left and increasing the display density is much more productive and fluid. This change seems to signal the death of the “iPad first” design. I’m pretty happy about that. The ability to work with field values while in design mode is fantastic.

The addition of the Data Table is brilliant. It’s much more fluid than swapping tabs to use the Data Model Viewer or building a temp table on the canvas.

I didn’t think I would like the “smart grid” feature but I’ve become a fan. With additional elements like Data Table the working canvas becomes relatively smaller and I’ve found it simplest to just add my chart in the general location and move on. As a final step I can switch to advanced mode and fine tune the chart locations and sizes if necessary.

Very cool feature to me is the new Chart Filters. This filter is implemented as a set expression applied to the data before expression set analysis is applied. So you now have three layers of data filtering — User Selections, Chart Filter, Expression (Measure) set analysis.

The chart filter is requested by a set expression in hypercube property qHyperCubeDef.qContextSetExpression . So you can use it in the Engine API if the engine level supports it. Potentially we could see Chart Filters available in a future QSEoW release. I think it would be useful to have Chart Filters as a master item for reusability and consistency.

If you are using QSDA Pro to evaluate your Qlik Sense apps (why not!), QSDA Pro release 2.1 supports capturing and parsing the new Chart Filters.

Want to up your Qlik Development game? Register for the Masters Summit for Qlik — Madrid 19 Sept or New Orleans 14 Nov — three days of advanced Qlik Developer training and tips.

-Rob

Share
Performance, Productivity, Qlik Sense, QlikView

If() Tips

4 Comments

Summary:  I offer some tips for writing better performing and easier to maintain syntax when using the Qlik If() function. 

The Qlik If() function is very powerful and  frequently appears in Qlik Sense and QlikView apps.

Expressions using multiple If() functions can easily get out of hand and become difficult to maintain or debug, as well as poor performers.

In this post I’ll offer some advice on avoiding If() pitfalls and tips to write easier to understand expressions.

The Qlik syntax diagram for the If function is:

if(condition , then [, else])

That’s perfectly clear to most people, but I prefer to think of it more like:

if(condition , true result [, false result])

Tip#1: If() does not short circuit.

Both the true & false branches are calculated even when only one is possibly true.  For example:

If(Only(Currency = 'LC',  Sum(Sales), Sum ([Sales LC])

In this case both Sum() expressions will be calculated even though only one value will be utilized.  In most cases this behavior is not of concern and in many applications will perform very well.   However, a nested If() with many possible branches or a large data set may perform poorly.

For more on the short circuit issue see “How to Choose an Expression“.

 

Tip#2: Use indentation sparingly.

The true or false result may be an additional, “nested” If(), which is where we start to see some ugly syntax.  Following traditional programming conventions many people automatically indent the nested if like this:

If(Sum(Sales) > 100000, 'Large',
    If(Sum(Sales) > 75000, 'Med', 
      If(Sum(Sales) > 50000, 'Demi',  'Small')
    )
)

Essentially,  the expression above classifies into one of four values.  I don’t think indentation  adds to the readability and indentation will lead you into “tab hell” when you get many possibilities.  I prefer to write this expression as:

If(Sum(Sales) > 100000, 'Large'
,If(Sum(Sales) > 75000, 'Med' 
,If(Sum(Sales) > 50000, 'Demi'
,'Small'
)))

No indentation, all the closing right parens collected on one line at the end. Makes it very easy in the expression editor to see that you have the right number of parens.

The leading (vs trailing) commas are my personal preference.  This make it easier to comment out logic and in my view, the comma belongs to the If that follows it, not the preceding If.

I think the above syntax makes it very easy to understand that I am choosing  one of four results, and what the rule is for each result.  Syntactically each If() is the else parameter of the preceding If().  I don’t think of the Ifs as “combined”, rather as “sequential”.

Do indent when you are using If() as the then parameter,  as shown in Tip#4 below.

 

Tip#3: Simplify by testing from high to low. 

The business rule that created this sample expression may have been stated to the Qlik developer like this:

“Classify sales of 0 to 50 000 as “Small”, 50 001 to 75 000 as “Demi”, 75 001 to 100 000 as “Med” and above 100 000 as “Large”.

The developer may faithfully translate the requirement into this expression.

If(Sum(Sales) > 0 and sum(Sales) <= 50000, 'Small'
,If(Sum(Sales) > 50000 and Sum(Sales) <= 75000, 'Demi', 
,If(Sum(Sales) > 75000 and <= 100000, 'Med'
,'Large'
)))

This returns the correct result. Testing from low to high values forces the use of “and” which makes the expression more complex than necessary and potentially slower to execute.  In my experience, testing from high to low, as in the Tip#2 example, yields a cleaner syntax.

 

Tip#4: Use “and” when you mean and.

Here’s a sample expression requirement:

When Sales > 1000 and Region=’US’, it’s “Mega US”. When Sales > 750 and Region = ‘UK’, it’s “Mega UK”. Otherwise it’s “General”.

I have seen this written as:

If(Sum(Sales) > 1000, 
    If(Region = 'US', 'Mega US'),
If(Sum(Sales) > 750, 
    If(Region = 'UK', 'Mega UK'), 
'General')

While the “and” requirement may be satisfied with a then-if  nesting, I find it clearer with the “and” keyword.

If(Sum(Sales) > 1000 and Region = 'US', 'Mega US'
,If(Sum(Sales) > 750 and Region = 'UK', 'Mega UK' 
,'General'
))

What if the requirement for  both US & UK were 1000?  You could argue that this is clear case for nesting in that there is a shared  condition and perhaps it would be a good practice to not repeat ourselves on the Sum(Sales).

If(Sum(Sales) > 1000, 
    If(Region = 'US', 'Mega US',
    If(Region = 'UK', 'Mega UK'), 'General'), 
'General')

Notice  we needed to repeat the ‘General’ result to cover the null case.  So it’s not super clean, but it may be worth it to not repeat the sum(Sales) calculation.  Generally I find the performance difference between “and” and “nested if” to be insignificant and tend to favor whatever is the clearer syntax for the given requirement.

What about Pick(Match())? 

I’ve heard it occasionally claimed that a Pick/Match combination will run faster than a nested If.   The expression might look like this:

Pick(
    Match(
      -1
      ,Region= 'US' and Sum(Sales) > 1000
      ,Region= 'UK' and Sum(Sales) > 1000
      , -1
    )
,'Mega US', 'Mega UK','General')

In my own testing and reading I’ve never found any performance advantage to Pick/Match.  That said, sometimes the syntax is appealing.

One thing I don’t like about Pick/Match is the distance between the condition list  and the result list. It’s fairly easy to get the lists  mis-aligned as the expression grows.

I  wish Qlik had a Switch type function like:

Switch (
  condition1 : result1
  [,condition2 : result2, ...]  
  [: defaultResult]
)

 

Tip#5: Simplify by using Column(n) or Measure Name

If your if() refers to something that has already been calculated in the chart, you can use the Column(n) function to refer to the value of a measure/expression column. For example, in a color expression:

If(Column(2) > 0, Green(), Red())

This can be much neater than repeating the expression text and typically runs faster as well.

If you are on Qlik Sense May 2021 you can use Master Measure names in the expression like:

If([Total Sales] > 0, Green(), Red())

[Total Sales] need not be a measure in this chart.

Both QlikView and Qlik Sense also allow you to reference the Label of a measure/expression column in the chart. In most versions the syntax checker will declare this an error even though it calculates correctly. I tend to avoid the label technique due to this confusion.

 

Tip#6: Don’t use If() as a chart filter

Use If when you want to dynamically select from two or more alternatives.  If should not be used simply to filter data like this:

Sum(If(Region = 'EU' and CYTDFlag = 1, Sales)

Filtering is best done with Set Analysis. The same expression written with a Set:

Sum({<Region={'EU'}, CYTDFlag={1}>} Sales)

Set Analysis is much faster than If.  If you are new to Set Analysis, you might initially find the syntax more challenging than If.  But SA  is much more powerful than If and well worth mastering.

 

Tip#7:  Consider the other conditional functions. 

Alt() and Coalesce() can be a more compact and elegant approach to testing for nulls. Instead of:

If(IsNull(SalesRep), Manager, SalesRep)

use:

Coalesce(SalesRep, Manager)
// If you want to consider empty and 
// blank strings as Null:
Coalesce(EmptyIsNull(Trim(SalesRep)), Manager)

When testing against a list of values,  instead of multiple If() or “or”, use the Match() or WildMatch() functions instead.

If (Match(StateCode, 'VA', 'TN', 'FL', 'GA'), 'South',  'Other')

 

I hope you find these tips useful.  You can use my QSDA Pro tool to quickly filter and examine all the uses of the If() function in a Qlik Sense App, located on-prem or in SaaS.

-Rob

 

Share
Productivity, Qlik Sense, Tools

Deconstructing Visualization Performance

Leave a comment

Have you ever had a Qlik Sense Visualization take longer to calculate than you like?  You may have been measuring the response time with QSDA (or the older QVF QS Document Analyzer) ,  Add Sense or a stopwatch.

Your chart is likely made up of several expressions — Measures, color expressions, maybe reference lines.  Which expression(s) are the hogs? Some? All?

In QSDA Pro version 1.4 I’ve introduced a “Viz Deconstruction” feature that calculates each chart expression individually and lets you see where time is spent.  In the QSDA Pro Viz page, click the  button under a Viz:

Clicking the  button will pop up the Viz Deconstruction dialog.  Each expression in the chart will be executed and reported individually.

For this chart I can see that one Measure takes much longer than any other expression.  Now I know where to focus my efforts if I want to improve response time.

Here’s another example. Big chart, lots of data, seven Dimensions.

The Measures require over seven seconds each to generate  7M+ output rows. Maybe less detail would result in better performance and a more usable chart.  I’ll uncheck a few Dimensions and 

Wow! Big improvement in calc time and a more manageable number of rows.

As a final example we’ll see that long calc times are not always due to Measures.

The Measures take only a few milliseconds to calculate.  But the color expression takes more then 1/2 second.  Can we do this coloring more efficiently?

You may already be deconstructing charts by making clones, commenting code and remeasuring.   Of course, you have to deal with the effects of caching and cleaning up after yourself.  QSDA Pro provides a much faster and more structured approach.

QSDA Pro is free to try with applications containing 100 or fewer objects.  If you want remove the limits, get support or acknowledge the value you get from QSDA, purchase a Monthly or Annual subscription.

I hope you find the Viz Deconstruction feature useful. I have!

-Rob

Note: There was a bug in the 1.4.0 version that kept the Deconstruction feature from calculating when using a DESKTOP Connection. This has been fixed in version 1.4.2.

 

Share
Productivity, Qlik Sense, QSDA, Tools

QSDA Pro as a Quality Tool

Leave a comment

I my last two posts I introduced QSDA Pro and the flag workflow.  In this post I’m going to show how you can use QSDA Pro to quickly uncover quality issues in your Qlik Sense App.

QSDA groups flags by category, and the “Quality” category is where I usually begin my app review.  Quality flags are used to indicate where something appears to be seriously broken such as:

  • An expression syntax error.
  • A reference to a Master Measure that no longer exists in the library.
  • A missing extension.
  • A bookmarked field that is no longer exists in the data model.

These are the kinds of things you want to discover before your users do!

It’s relatively easy to create new errors in an existing Qlik app.  Qlik does not warn or block you from deleting a resource like a Field or Measure that is in use.

A properly done field rename may update names in expressions.  But it will not update variables or bookmarks.  And I’ve seen plenty of field renames go wrong and break expressions as well.

It can be difficult to detect all app errors by visually checking your app.  The broken piece may be a color expression or a calculation condition that isn’t readily visible.

So yes, easy to break, sometimes hard to detect and heros find  problems before their users do.  Enter QSDA Pro.

In QSDA menu,  Help -> Flag List will display a list of all potential flags in the installed version. Here’s the current list for Quality (The “{}” bits are values that will get filled in when the flag is created.)

Some of these are fairly straightforward, some a bit more subtle.

Why would you have a missing Master Dimension or Measure?  A fairly common scenario is not understanding that Master Items are referenced by internal Id, not by Name.  So you create something called “Sales” and use it in several charts.  Later you are doing some exploration and create something called “Sales2” which you like better. So you think if you delete “Sales” and rename “Sales2” to “Sales” everything will be using the new “Sales”.  Wrong.

Because the master item is now gone, I can’t tell you what used to be in this master item.  You will have to rely on an app backup. Or…if you have a previous QSDA Analysis for this app you can look up the master item definition there!

All Expressions and Dimensions are validated using the Qlik syntax checker. A typical error might be a bad fieldname (perhaps because the field was removed from the model).

Flag Details will give us a detailed error message, location of this expression  (Master Library) and importantly the Use Count. This use count is zero so we know deleting the measure is a viable option.

QSDA uses the Qlik syntax checker so the test is only as good as what I  get from Qlik.  There are some limitations, for example when column labels are used in an expression.  You’ll note that the Qlik expression editor flags this as invalid so QSDA will pass this message on.

The “Parse Error” flag is raised when Qlik Syntax says the expression is ok but QSDA finds the expression does not conform to expected syntax rules. This could be a problem with the QSDA parser  (a work in progress) or it could be Qlik tolerating something unusual.  For example, do you think this is a valid expression?

sum({<Sales={">10 between < 90"}>}Sales)

The Qlik syntax checker will declare it valid. And it will return an official looking result — which may or may not be correct.  But what is that “between” keyword? (if you’re curious why “>10 के बीच < 90” also seems to work, see https://masterssummit.com/something-to-get-confused-about/).

Nervous about refactoring or changing your apps?  Can you relate to “Change Paralysis”  or Fear Driven Development (FDD)?  We all need a tool like QSDA Pro to proactively plan changes as well as audit after the fact for unintended errors.

Download QSDA Pro now and analyze some of your own apps.  QSDA Pro is free to use during the beta period.

You can analyze published and unpublished apps, so go ahead and take a look at some of those production apps.   Let me know in the comments if you find something interesting!

-Rob

 

 

 

Share
Productivity, Tools

QViewer + Easymorph, Better Together

Leave a comment

Thousands of Qlik Developers use my QViewer QVD Viewer tool every day to browse inside QVD files.  Sometimes you want to do more than browse. I get questions like

  • Can I sort the rows?
  • I want to sum field x grouped by field y. Can I do that?
  • Can I get a histogram of field values?

The answer to all of the above — and more — is Yes!  When you install the free Easymorph Data Prep and ETL tool, you can use the powerful features of Easymorph like an extension to QViewer.  With Easymorph you can sort, query, transform and profile your QVD data.

When viewing a QVD, press the button on the menu bar. The current QVD will open seamlessly in Easymorph.  If Easymorph is not yet installed you will be taken to a webpage with download and install instructions.

You can also open Easymorph by right-clicking a QVD from QViewer folder view.

The QVD will open in Easymorph and you can then use all of Easymorph’s powerful features with your QVD.

Easymorph has plenty of help and an active Community to help you get started.  I’ve also prepared a couple of Help articles in QViewer showing some typical QViewer/Easymorph  tasks.

-Rob

 

 

Share
Productivity, Qlik Sense, QlikView, Tools

Data Browser Tricks

1 Comment

Summary: I demonstrate my latest “Data Browser” sheet for use in Qlik data modeling. Download here.

Today I want to share the latest version of my “Data Browser” sheet.

What’s a “Data Browser”? It’s my name for something you may already have. The ubiquitous “System Sheet” or sometimes “Profiler” where you may have listboxes or charts utilizing the system fields “$Field”, “$Table”, “$Rows”.  These fields are part of the shadow data model that hold useful metadata about the data loaded in your app.

Some very useful example profile sheets have been shared in the community over the years.

You can download the latest QlikView and Qlik Sense versions of my Data Browser here.

For QV, I just copy the objects into my new app in one go. For QS,  I either copy the charts one-by-one or if I’m lucky they are already in my template (who’s going to build the sheet copy extension?) .

I’ll do this walk through with the QV version, the QS version is similar.

When I make selections in $Field I can see values and frequency counts in a listbox. After selecting for example the “Sales” field, the values and frequency counts are shown in a listbox. I also have a  histogram and some descriptive statistics about the field values.

I can select field values and drill into data using  this sheet or my application sheets. For example, I might want to select the outlier high Sales value, then select Product in $Field to find out what Product is associated with this Sale, clear the Sales field to see what other Sales look like for this product and so on.

There are several properties such as Null Count (Information Density), field alpha/num content ($tags) that I can get from the built in Table Model Viewer.  The Viewer requires me to examine one field at a time. In the chart below I get an overview of those properties of interest.  Because it’s a chart, I can use sorting and selecting to focus.

Something I don’t get from the Table Viewer is the difference between numeric count and text count for a field.  I like to surface this problem (highlighted in yellow) early in my modeling.

One of my favorite features is the “Value Association” chart below. It’s likely a favorite because it took me a long time to work out the expression!

In  Table Viewer we use “Subset Ratio” to understand where we have connected and un-connected data in our model.  Subset Ratio is limited to reporting the relationship between key fields. It can’t tell me how data field  values in this table associate to data field values in other tables.  Subset ratio, like other stats in the Viewer, does not respect selections. For example, if I select a specific Customer, how many SalesReps are linked to the Customer?

The chart below (highlights added) covers all these use cases and can also surface problems in the model.

I’ll start by selecting a field central to my model, in this case “OrderID”.   I then sort by the “Pct” column.  This column represents the percentage of field values associated to the selected field,
“OrderID”.

In the green highlight, I’ve called out a Table “Sales” that has zero association with Orders. Something to look into.

In the orange callout, I can see that only 19% of my Employees are associated with Orders.  That might be a candidate for trimming the Employees table when loading.

In the red callout, I see something puzzling. Only 47% of my OrderDates are linked to Orders. That may be ok, I would need to review the data.  What looks not ok is that WeekDay Pct is also 47%. I would have expected something like 5/7 or 6/7. No fraction of weekdays would equal “47%”.  And there are 1092 values for WeekDay…something is off.

I’ll select WeekDay from the chart and examine the values in the listbox. Aha, the Weekday values were created incorrectly.  They were created with a Date() function instead of the correct WeekDay() function.

I used to hide the sheet before production but now I generally leave it in as I and others find it useful in production as well.

Besides the Table Viewer, another Qlik modeling tool I really like is Catwalk. If you haven’t used Catwalk I encourage you to check it out.  I won’t go in to explaining Catwalk as it has an excellent tutorial built in.

Do you have any favorite profiling or modeling tools to use with Qlik?

-Rob

Share
Performance, Productivity, Qlik Sense, QlikView, Scripting

Creating Temporary Script Associations

8 Comments

Summary: I review using Join, Lookup() and ApplyMap() as script techniques  to calculate using fields from multiple tables. I ultimately recommend ApplyMap().

Qlik charts  can calculate values on the fly, using fields from multiple tables in the model.  The associative model takes care of navigating (joining) the correct fields together.  Our expression syntax doesn’t  identify what table a field exists in — the associative logic takes care of this detail for us.

We may want to calculate a measure, for example, “Net Amount”, applying a business rule requiring fields from several tables:

Our expression to calculate “Net Amount” might look like this:

Sum(Amount) - 
RangeSum(
  Sum(Quantity * UnitCost), 
  Sum(Amount * Discount), 
  Sum(Amount * SalesTaxRate), 
  Sum(Amount * ExciseTaxRate)
)

There may be cases (such as performance) where we want to pre-calculate “Net Amount” as a new field in the script.  In script, we don’t have the magic associative logic to assemble the fields.  When a script expression is used to create a new field, all fields must be available  in a single load statement.  This is straightforward when all the required fields are in the same table.  But what do we do when the fields come from multiple tables?

Here are three approaches to solving the problem of calculating a new field using multiple tables in script.

  • Join
  • Lookup() function
  • ApplyMap() function

I’ll demonstrate deriving the same “Net Amount” calculation in the script.

JOIN

The Join option will require us to execute multiple joins to assemble the fields onto each Orders row and then finally do the calculation.  The script might look like this:

Left Join (Orders)
LOAD
 ProductId,
 UnitCost
Resident Products
; 
Left Join (Orders)
LOAD
 CustomerId,
 Discount,
 State
Resident Customers
; 
Left Join (Orders)
LOAD
 State,
 SalesTaxRate,
 ExciseTaxRate
Resident States
;

NetAmount:
LOAD
 OrderId,
 Amount - RangeSum(
   Quantity * UnitCost,
   Amount * Discount,
   Amount * SalesTaxRate,
   Amount * ExciseTaxRate
 ) as NetAmount
Resident Orders
;
// Drop the extra fields from Orders.
Drop Fields State, UnitCost, Discount, SalesTaxRate,ExciseTaxRate
From Orders
;

It’s a fairly good option.  It can be a lot of code depending on how many fields and tables we need to traverse. We need to be aware of “how many hops” between tables and may require intermediate joins (State field) to get to the final field (SalesTaxRate & ExciseTaxRate).

When using Join we need to ensure we have no duplicate keys that would mistakenly generate additional rows.

LOOKUP

Lookup() seems the most natural to me. It’s the least amount of code and it even sounds right: “look-up”.  It’s a one-to-one operation so there is no danger of generating extra rows.

It’s my least used option due to performance as we shall see.

Lookup takes four parameters  – a field to return, the field to test for a match, a match value to search for and the table to search.  Using Lookup() our script will look like this:

NetAmount:
LOAD
 OrderId,
 Amount - RangeSum(
   Quantity * Lookup('UnitCost', 'ProductId', ProductId, 'Products'),
   Amount * Lookup('Discount', 'CustomerId', CustomerId, 'Customers'),
   Amount * Lookup('SalesTaxRate', 'State', Lookup('State', 'CustomerId', CustomerId, 'Customers'), 'States'),
   Amount * Lookup('ExciseTaxRate', 'State', Lookup('State', 'CustomerId', CustomerId, 'Customers'), 'States')
 ) as NetAmount
Resident Orders
;

Note that for SalesTaxRate and ExciseTaxRate, the third parameter — the match value — is another Lookup() to retrieve the State. This is how we handle  multiple hops, by nesting Lookup().

It’s a nice clean statement that follows a simple pattern.  It performs adequately with small volumes of data.

Lookup does have a significant performance trap in that it uses a scan  to find a matching value.  How long to find a value is therefore dependent on where in the field the value is matched.  If it’s the first value it’s very quick, the 1000th value much longer, the 2000th value exactly twice as long as the 1000th. It’s a bit crazy making that it executes in O(n) time, for which I prefer the notation U(gh).

APPLYMAP

I like to think of the ApplyMap() approach as an optimized form of Lookup().  We first build mapping tables for each field we want to reference and then use ApplyMap() instead of Lookup() in the final statement. Our script will look like this:

Map_ProductId_UnitCost:
Mapping
Load ProductId, UnitCost
Resident Products
;
Map_CustomerId_Discount:
Mapping
Load CustomerId, Discount
Resident Customers
;
Map_CustomerId_State:
Mapping 
Load CustomerId, State
Resident Customers
;
Map_State_SalesTaxRate:
Mapping 
Load State, SalesTaxRate
Resident States
;
Map_State_ExciseTaxRate:
Mapping 
Load State, ExciseTaxRate
Resident States
;
NetAmount:
LOAD
 OrderId,
 Amount - RangeSum(
   Quantity * ApplyMap('Map_ProductId_UnitCost', ProductId, 0),
   Amount * ApplyMap('Map_CustomerId_Discount', CustomerId, 0),
   Amount * ApplyMap('Map_State_SalesTaxRate', ApplyMap('Map_CustomerId_State', CustomerId, 0)),
   Amount * ApplyMap('Map_State_ExciseTaxRate', ApplyMap('Map_CustomerId_State', CustomerId, 0))
 ) as NetAmount
Resident Orders
;

The mapping setup can be a lot of code depending on how many fields are involved. But it’s well structured and clean.

In the final statement, we are “looking up” the value using ApplyMap() and it performs very quickly.  ApplyMap uses a hashed lookup so it does not matter where in the list the value lies, all values perform equally.

We can re-structure and simplify the mapping setup and subsequent use with a subroutine like this:

Sub MapField(keyField, valueField, table)
// Create mapping table and set vValueField var // equal to ApplyMap() string.
 [Map_$(keyField)_$(valueField)]:
 Mapping Load [$(keyField)], [$(valueField)]
 Resident $(table);
 Set [v$(valueField)] = ApplyMap('Map_$(keyField)_$(valueField)', [$(keyField)]);
End Sub

Call MapField('ProductId', 'UnitCost', 'Products')
Call MapField('CustomerId', 'Discount', 'Customers')
Call MapField('CustomerId', 'State', 'Customers')
Call MapField('State', 'SalesTaxRate', 'States')
Call MapField('State', 'ExciseTaxRate', 'States')

NetAmount:
LOAD
 OrderId,
 Amount - RangeSum(
 Quantity * $(vUnitCost),
 Amount * $(vDiscount),
 Amount * $(vSalesTaxRate),
 Amount * $(vExciseTaxRate)
 ) as NetAmount
;
LOAD
 *,
 $(vState) as State
Resident Orders
;

Note the use of the preceding load to handle the nested lookup of State.   You could also modify the Sub to handle some level of nesting as well.

I typically use the mapping approach as I find it always gives accurate results (with Join you must be careful of duplicate keys) and generally performs the best, and importantly, consistently.

Whether you are new to Qlik or an old hand I hope you found something useful in reading this far.

-Rob

 

 

Share
Productivity, Qlik Sense, Visualization

Using JSON as HyperCube Data Payload

1 Comment

Summary: In this post I suggest a case for formatting measures as JSON to allow for easy consumption in an Engine-API webapp.

In a recent customer webapp project with my partners at Websy, I found that using JSON for data in Qlik hypercubes was a useful technique.

The app displays an overview of metrics collected throughout the organization, along with contextual clues and drill & focus capabilities.

The data consists of a varying number of metrics or measures  Measures may be added or removed by appearing in the data. Contextual data such as labels and color coding is provided in the data.

I can’t share the actual customer app.   Here is a simplified data example  to demonstrate with.

 

The real data contains additional complexities, but this sample will suffice.  Using this data, a section of the dashboard output would  appear like this:

 

A row is generated for each Category and columns are generated for each measure.

Current month values, (“Flag_Current=1”),  are displayed alongside available history (“Flag_Current=0”)  for the Date selected in the “Compare to” dropdown.

The CompareDirection is used to to control whether an increase in this measure should be scored as positive  or negative.

There is some asymmetry in the data.  Attributes such as “Format”, are available only in the Current data but are required in the History data as well.  History is available for some measures, but not others.  So what’s the best way to handle the asymmetries?  Not uncommon in Qlik, we can use set analysis and expression functions such as “Aggr()” or “TOTAL”  to propagate the attributes.

As the application requirements and data model grew, the expressions  and sets became more complex and difficult to manage and validate. This was true whether we provided the data as additional columns or qAttributes.

On the javascript side the row and column data are  processed into an object structure to prepare for visualization.  The type of propagation required here — assigning current.Format to history.Format — is trivial in javascript.

Since we are going to transform this data into objects, I thought why not deliver the data as objects already?

The text representation of a javascript object is JSON. Here’s what the Qlik table will look like with JSON output.

 

I created a Qlik script variable  to help generate the JSON.

Set AsJson = '"$1": ' & if(IsNum($1) and text($1) = num($1), $1, '"' &  $1 & '"');

The measure for the “Current Values” column generates an array of objects. The Measure expression is:

'[' & 
concat({1<Flag_Current={1}>} 
'{'
& $(AsJson(Date))
& ', ' & $(AsJson(Label))
& ', ' & $(AsJson(Value))
& ', ' & $(AsJson(Color))
& ', ' & $(AsJson(Format))
& ', ' & $(AsJson(SortOrder))
& ', ' & $(AsJson(CompareDirection))
& '}' , ', ', SortOrder)
& ']'

The definition for the “History Values” is  similar with only the set expression and the fieldnames changing.  The set expression to get this “collection” of data is specified only once and we don’t have to be concerned with repeating the set in multiple definitions.

Javascript to consume the data might look like this.  Note the JSON.parse() to consume the JSON.

const columns = [];
layout.qHyperCube.qDimensionInfo.forEach(info => 
  columns.push(info.qFallbackTitle));
layout.qHyperCube.qMeasureInfo.forEach(info => 
  columns.push(info.qFallbackTitle));
const senseData = layout.qHyperCube.qDataPages[0].qMatrix;

senseData.forEach(row => {
  let currentValues =   
    JSON.parse(row[columns.indexOf('Current Values')].qText);
  let historyValues = 
    JSON.parse(row[columns.indexOf('History Values')].qText);
  let category = row[columns.indexOf('Category')].qText;
  renderRow(category, currentValues, historyValues);
});

I find several advantages in the JSON approach.

  • Fewer expressions and sets to maintain.
  • A closer match between the HyperCube and the object model used in the app, making for easier understanding and validation.
  • Less transformation code on the javascript side.

I’m not suggesting that JSON is always the best way to deliver data.  We found it useful in this particular case and I wanted to share the experience so you could keep it in your tool bag as well.

-Rob

Want to learn more on this topic or advanced Qlik development?   Join us at the Masters Summit for Qlik in Amsterdam (28-30 Oct) or Washington DC (6-8 Nov). For traditional Qlik Devs, we’ll be teaching  advanced skills in data modeling and expressions in the QS/QV Track.  Experienced JS developers will want to attend the Qlik API Track for a deep dive into creating webapps and mashups.

Share