All posts by Rob Wunderlich

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

 

Share

Install QS Document Analyzer on Server

Some people wish to run QS Document Analyzer (QSDA) on the Qlik Sense Enterprise server rather than Qlik Sense Desktop.  While the current installer only supports desktop, you can manually install to the server.

QSDA installs three components; a connector, an application (qvf) and an extension.

Install the latest QSDA on a desktop machine to unpack the components.  You don’t even need QS Desktop installed.  Take note of the folders where the components were installed.

Using the QMC,  upload the qvf application to the server.  Zip up the qsda-ribbon extension folder and upload as an extension through the QMC.

Copy the QsAppMetadataConnector folder from the install folder to this folder on the server:

C:\Program Files\Common Files\Qlik\Custom Data

If you are running a multi-node cluster, the connector must be installed on the central and rim nodes.

That’s it!

 

Share

Masters Summit Fall 2019

Fall 2019 dates and location for the Masters Summit for Qlik have been announced .  Our goal at this  hands on education event is to “Take your Qlik skills to the next level”  to make you more productive and increase the business value of your QlikView or Qlik Sense applications.

When registering for either location, choose one of two tracks:

Our traditional “Qlik Sense and QlikView” track is designed for Qlik Developers who create applications using the out-of-the box clients: Qlik Sense Hub or the QlikView Desktop. Through lecture,  hands on activities and takeaway code samples,  the Summit will expand your knowledge with advanced techniques and a deeper understanding of the core skills required in all Qlik application development:

  • Data Modeling
  • Advanced Scripting
  • Advanced Aggregation & Set Analysis
  • Visualization.
  • Performance

See the complete agenda here.

Our Qlik API track is designed for Web Developers who create Qlik applications and mashups using the Qlik Engine API, Qlik Core, QAP and custom visualizations.

  • Qlik Core
  • Generic Objects
  • Loading & Visualizing Data
  • Architecture and Performance

The API track is led by Nick Webster and Speros Kokenes. You can see the agenda for the API track here.

Our evening guest speakers, networking events and optional lunchtime lectures fill out the program with additional content and lively discussion.

Our panel of six presenters are well known as authors, educators,  Qlik experts and members of the Qlik Luminary and MVP programs.

Learn more about determining if the Summit is right for you and choosing a track in our Frequently Asked Questions.  If you have any unanswered questions or want to learn more, reach out to registration@masterssummit.com .

I hope you can join us in Amsterdam or Washington D.C. to take your Qlik skills to the next level.  Event details and online registration.

See you there!

-Rob

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

Qlik Sense Document Analyzer V1.5

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

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

Here are the significant changes in V1.5:

The Installer allows editing of all install paths and installation will create a log file in the User’s temp directory.

The  Summary ribbon at top of each sheet provides for sheet navigation by clicking a cell.

 

Improved error handling in the connector. Certain types of errors, such as a chart calculation timeout, will not terminate the script. Error count is reported on Summary sheet and detailed error messages can be viewed on the “Extract Log” sheet.

Items that should be considered incomplete due to analysis errors will highlighted in yellow.  This highlighting is a work-in-progress as I discover new possible errors.

Field widths (Symbol width) are no longer estimated! Field sizes are obtained directly from the engine.  Due to this change, this version will report different values for field sizes than previous releases, generally +/- 10%.

The connector edit dialog has been improved.  Error messages now appear properly in their own window and a progress bar displays when the list of applications is fetching.

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

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

The only installation supported by the installer is QS Desktop. You can use QS Desktop to analyze applications on a server.  It is possible to manually install on a server by uploading the application (qvf), the qsda-ribbon extension and the connector directory if you have the need and want to give it a shot.  I do plan to create a supported server installer in the future.

Have fun!

-Rob

Share

Web Dev for Qlik Devs in US & UK March 2019

March 2019 brings our popular boot camp course to both the US and UK. Here’s an opportunity to fast track your Qlik team in using Qlik Sense APIs to create extensions, mashups, portal integrations and custom content pages that leverage data and visualizations from Qlik Sense.

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

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

See the course syllabus here.

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

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

The US session will be taught by Rob Wunderlich and the UK session by Nick Webster.

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

Cost for the four days is $2,600 / £ 2,000 and includes all course materials and lunch each day. Register at http://websy.io/training/web-dev-4-day

Questions? Reach out to us. See you there!

Share

Masters Summit Stockholm

The 16th edition of the Masters Summit for Qlik will take place 1-5 April 2019  at the Stockholm City Conference Centre .  Our goal at this  hands on education event is to “Take your Qlik skills to the next level”  to make you more productive and increase the business value of your QlikView or Qlik Sense applications.

 

New for this summit, you can choose from two tracks:

Our traditional track, 1-3 April, is designed for Qlik Developers who create applications using the out-of-the box clients: Qlik Sense Hub or the QlikView Desktop. Through lecture,  hands on activities and takeaway code samples,  the Summit will expand your knowledge with advanced techniques and a deeper understanding of the core skills required in all Qlik application development:

  • Data Modeling
  • Advanced Scripting
  • Advanced Aggregation & Set Analysis
  • Visualization.
  • Performance

See the complete agenda here.

Our new API track, 3-5 April, is designed for Developers who will create Qlik applications and mashups using Qlik APIs, Qlik Core, QAP and custom visualizations.

  • Qlik Core
  • Generic Objects
  • Loading & Visualizing Data
  • Architecture and Performance

The API track is led by Nick Webster and Speros Kokenes. You can see the agenda for the API track here.

Our evening guest speakers, networking events and optional lunchtime lectures fill out the program with additional content and lively discussion.

Our panel of six presenters are well known as authors, educators,  Qlik experts and members of the Qlik Luminary and MVP programs.

Learn more about determining if the Summit is right for you and choosing a track in our Frequently Asked Questions.  If you have any unanswered questions or want to learn more, reach out to registration@masterssummit.com .

I hope you can join us in Stockholm to take your Qlik skills to the next level.  The early bird registration discount is available until 28 February.  Event details and online registration.

See you there!

-Rob

Share

Qlik Sense Document Analyzer v1.4

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

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

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

 

Here’s what’s new in version 1.4:

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

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

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

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

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

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

-Rob

 

 

Share

Loading Varying Column Names

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

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

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

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

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

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

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

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

It’s magic I tell you!

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

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

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

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

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

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

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

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

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

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

-Rob

 

 

Share