Category Archives: Productivity

Data Browser Tricks

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

Creating Temporary Script Associations

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

Using JSON as HyperCube Data Payload

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

A Common SSE Plugin Project

Summary: I introduce qcb-qlik-sse, a community Server Side Extension to share custom Qlik functions. 

At the Masters Summit for Qlik, I dive into several different methods of creating reusable script and custom functions.  In QlikView we have the ability to write custom functions using VbScript/Jscript in the qvw Module.

Custom functions have been useful for things like regular expressions, geo calculations, url encoding, encryption and others.  I’ll call them “edge functions” — some of us need them, some of us don’t.

Qlik Sense does not have the module facility. How can we satisfy the requirement for custom function in Qlik Sense?  The Server Side Extension (SSE) facility can fill the need and is available to both Qlik Sense and QlikView.

An SSE Plugin runs as a separate task and provides communication with a Qlik Script or chart Expression via a TCP port. The same SSE Plugin can serve both QS and QV.

Anyone can write an SSE. The Qlik team provides the SSE base and you write a plugin that wires your new functions to Qlik. The new functions can be used in both Script and Charts.  A number of plugins have already been produced.

SSE seems to be the ideal place to provide a collection of edge functions.  Rather than a bunch of one-offs, I’m thinking a good idea would be to pool resources into a single effort that could be shared, much like QlikView Components did for Script.

I’ve implemented this idea as qcb-qlik-sse.  This server uses as it’s base the qlik-sse package created by Miralem Drek.

qcb-qlik-sse is written in javascript and runs in node.js.  At startup, the server scans it’s “/functions” directory and discovers what functions are available.  The general idea is that you can add new function by creating a new js file.  You can remove function you don’t want available by deleting the corresponding js file.

See what functions I’ve already implemented in the doc here. I’ve also provided an example qvf and qvw that exercise the functions.

If you want to try it out,  download the project and define the plugin to QS or QV as documented here.  You will also need node.js 8 or later installed.

Defined functions will show up in the suggestion list in both the script and expression editors.

 

If you want to add functions, some javascript skills are required.  Follow the directions in the readme and submit a PR.

I’ve labeled the project as “experimental” at this stage because I anticipate there could be some significant restructuring as I get feedback.

Let me know your ideas and if you find this useful!

-Rob

Want to kick the tires on reusable code and make your Qlik team more efficient? Come to the Masters Summit for Qlik, a three day advanced training event for Qlik Developers. 

 

 

Share

Form Input and Commenting in Qlik

A common desire from Qlik customers over the years has been the ability to interactively edit data on a QV sheet and persist the changes in Qlik or maybe write back to another system. As Martin Mahler of VizLib describes it “turning a one-way data street into a two-way street”.

Sometimes the term “writeback” is used to describe the idea of inputting data in a Qlik app and persisting that data — writing back  the data — to some other system.  The new data is saved and shared with other users of the app. Importantly,  in a form/writeback application, the added data is associated with some row of the data model. For example, in a warranty claims app we may have a dropdown that allows the user to categorize each claim.  The assigned category is used in further analysis.

I think of “commenting” as making annotations on a chart, or chart data point, and a given set of selections.  Ideally, comments may turn into a discussion and use some sort of notification mechanism.

These are not pure terms. There are overlaps to be sure.

I’ve seen some interesting bespoke implementations by partners.  There is also a growing list of off the shelf products that enable writeback and or commentary within Qlik Sense or QlikView.

Some products are Qlik Sense only, some work with both QlikView and Qlik Sense.

Most products allow you to create table sheet objects that mix Qlik DImensions and Expressions with additional input fields such as freeform text, dropdowns or checkboxes.  They all persist the data to some type of backend store such as a database.

When evaluating a product for your requirements, here are some items to consider:

  • Do you have requirements for read-only and update users?
  • Are you looking to add additional data in a single chart or do you need to reference the new data from multiple charts?
  • Are you looking to add one to one new data or build complex workflow apps?
  • Does your business objective require structured form data,  free-form commentary or both?

There are an interesting range of products and capabilities out there.  Klikins and Emark Forms for example let you add new fields to a straight table.  One of the more interesting approaches is K4 Analytics, which embeds Excel into Qlik. This provides the full range of Excel formula and formatting functions. You can build some pretty powerful aps this way.

There are products that focus on finance reports. TrueChart creates a set of functional and beautiful IBCS compliant reports along with a nice navigation interface that can be reused throughout the Qlik app.

Both TrueChart and Climber Finance Report support the type of commenting and annotations user require in finance reporting.  I’m excited that the Climber commenting is being expanded and released as a generic commenting & collaboration product by VizLib. Qommentary is another global commenting solution.

Here in no particular order are some Writeback / Commenting products I’m aware of. The headings are links to the vendor site.

I’m sure I’ve missed some products.  Feel free to leave me a comment if you have something to share.  It would also be good to hear about use cases where you have found value in implementing an input/writeback solution.

Inform Write 

QlikView and Qlik Sense

K4 Analytics

QlikView and Qlik Sense

TrueChart

Qlik Sense

Emark Forms

Qlik Sense

Klikins

QlikView and Qlik Sense

Pomerol Writeback

Qlik Sense

Qommentary

Qlik Sense

VizLib 

Qlik Sense

Komment

Qlik Sense

Share

Catwalk — The Alternative Data Model Viewer

Summary: I introduce “catwalk”, an alternative data model viewer for Qlik Sense from the Qlik oss team.  Don’t like reading?  Go to https://catwalk.core.qlik.com/ and give it a spin. 

I’ve been having a lot of fun with catwalk, a fairly new tool from the Qlik oss team.   I call catwalk an “alternative data model viewer” for Qlik Sense.

I’m going to start by showing a few screenshots and then tell you how to get started using catwalk.  It’s easy to try out.

After selecting an app to view, you’ll get a graphical table & field layout.  In addition to a visual depiction of linkage, you’ll get some rich information about cardinatility, relationships  and some nice explanations of subset ratios.

You can also make field selections and see how those selections impact the other tables.  A nice little tool in the lower right corner lets you build hypercubes (straight tables) on the fly to visualize aggregations.

I’m not going to tour all the features here because the first time you enter catwalk you’ll be offered a walkthrough guide.  I highly recommend you take this brief guide.  You can return to the guide at any time from the … menu in the upper right.  A tip on the guide: When it says “you can do X, try X” it won’t let you continue until you try actually try X.  Clever.

 

So how do you get access to all this goodness?  Go to the github page https://github.com/qlik-oss/catwalk for instructions.  Don’t like reading instructions?  Make sure your QS Desktop is started and go to the hosted version at  https://catwalk.core.qlik.com/.  Have fun.

The connection to your Qlik Sense server is from your local browser. No data is passed to the server hosting catwalk.

A really cool way to invoke catwalk is to set up a bookmark with the javascript shown here https://github.com/qlik-oss/catwalk/tree/master/bookmark.  Click the bookmark while in any app in the hub and you’ll open catwalk on that app.   Simple.

So while catwalk may have been conceived  as a data model explorer for Qlik Core (which has no built-in viewer) it’s just as valuable for Qlik Sense Enterprise or Desktop.

Have fun!

Share

Understanding and Using Subset Ratio

Summary: If you are familiar with subset ratios in Qlik, you may not find much new in this post. But if you are new to Qlik or are unfamiliar with subset ratios in your data model, please read on. 

When loading data into Qlik and building a new data model, inspecting the Subset Ratio of key fields is an important exercise to ensure data quality.

Subset Ratio is displayed in the Preview Pane (Qlik Sense Data model viewer) or Table Viewer (QlikView) when a key field (field linking two or more tables) is selected.

After clicking a key field, in the Preview pane you will see three important numbers:

Total distinct values:  The count of all distinct values for this field (CustomerId) from all tables (Orders” and “Customers”) in the model.

Present distinct values:  The count of distinct values for this field (CustomerId) in the currently selected table (Customers).

Subset ratio: “Present distinct values” divided by  “Total Distinct Values”.  What percentage of total field values are represented in this table?  In this case, 100% of all CustomerId values in the data model are represented in the Customers table.  This is good. We will typically expect to see 100% Subset ratio in a dimension table.

Let’s take a look at the Subset ratio for the same field in our fact table — the Orders table,

It’s less than 100%.  Our Customer table is our “customer master” and represents all of our potential customers.  Our Orders table represents a limited time period, perhaps 12 months.  Only 44 distinct customers, or 22%,  are represented in the set of Orders we have loaded.

Less than %100 Subset ratio is a normal condition for a Fact table.  If we don’t want to include Customer data for those customers who have no orders, we can filter the Customer load with a “Where Exists(CustomerId)” clause.

So far we’ve seen “normal” subset ratios.  Let’s look at some exceptions.  What does it mean when the dimension table (Customers) has less than 100% subset ratio?

It means we have an order(s) that has no link to a Customer row.  That’s a data quality problem. In the example above we can see that we have 1 missing CustomerId (201 – 200).

Why do we have a missing Customer?  We would have to dig into the data to find out why.  It could be that we have loaded historical orders and “inactive” customers are archived from the Customers table.  It could be that we have some bad data due to a bug.  We have to analyze the data and decide on the best path to remediate.

By the way, what is the specific value(s) of the missing CustomerId?  A simple way to make this determination is to create a table chart with two columns — The key field and a field that has 100% density (every row has a value) from the Customers table.  I’ll use the CustomerName field. Sort the table by CustomerName and the key value in question will show at the bottom of the table with a null value for CustomerName.

What does it mean when the sum of the subset ratios for two tables equals 100%? It means there are no matching values between the two tables.   This can happen for instance when the keys come from two different systems that use slightly different nomenclature.  Perhaps in your ERP all ProductId values start with “P” but in the spreadsheet that someone provided for additional part info the “P” is excluded because none of the humans use the “P” when identifying parts.

Examining Subset ratio as you build up your data model is an important quality step.  Validating the quality of your data model will make the process of creating visualizations go much smoother.

-Rob

 

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

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

Exploring Data Lineage with NodeGraph

In the Qlik world, we are frequently faced with questions like:

  • Where did this field come from?
  • What applications (if any) use this QVD?
  • If I change this database table, what applications will be affected?
  • Am I creating QVDs that are not being consumed?

I’ve maintained a QlikView lineage application for my customers over the years.  I was never completely happy with it as Qlik lineage metadata is inconsistent and the critically important field level lineage was never available.

All that changed when I discovered NodeGraph.  Now I’m a NodeGraph fan and partner.

NodeGraph is an add-on for your QlikView and Qlik Sense solutions that allows you to explore, visualize and trace where your data comes from, whether it’s up-to-date, how it’s calculated, and much more.

NodeGraph scans both QlikView and Qlik Sense files and produces a graph of applications and data in a beautiful easy to navigate interface.  Select any node, for example a dashboard QVW, and lines trace the data lineage through transformed QVDs all the way back to the source database.

 

You can search, filter and drill in any direction;  from applications,  QVDs, source tables, fields, charts or SQL queries.

Where did this field come from?  Right-Click any field and select the Field Explorer to get a report of the complete lineage for this field, including all script transformations!

 

I’m looking at a chart that shows a count of Customers.  How was that count calculated? Where did the data come from?  In the Content view I can review the expression and see the lineage for fields used.

 

One of my favorite features is the one-click application documentation.  The tool generates a pretty printable/PDF document suitable for Auditors that describes all data sources, transformations, and expressions used in the selected application.

 

There are more uses and features in NodeGraph than I can cover in a blog post, including governance reports, application usage, GDPR compliance and testing.

If you are coming to Qonnections,  see NodeGraph in action at the TechLab10 booth (#603) in the Discovery Expo.

Learn more about NodeGraph at nodegraph.se. If you have questions or would like to schedule a free demo,  reach out to the NodeGraph team on the website or info@nodegraph.se.  If you are in North America you can contact me directly for questions or to scheduling a demo.

-Rob

Share