EasyQlik QViewer acquired by Rob Wunderlich

Effective January 1st, 2018 EasyQlik QViewer has been acquired by Rob Wunderlich. 

I believe it's a great outcome for the product, its users and customers. It was a bit challenging for me to keep focus on QViewer and EasyMorph simultaneously, which resulted in a slower development pace for QViewer. It's hard to imagine a better new owner than Rob who is well known in the Qlik community and who surely has a great vision on what would make QViewer even more useful.

From now on, the existing licensed QViewer customers should contact support@panalyticsinc.com for all questions related to QViewer. The website http://easyqlik.com keeps operating as usually.

I, from now on, focus solely on EasyMorph.

Read also Rob's statement on the acquisition.

Understanding bar charts in QViewer listboxes

Starting from version 3 listboxes in QViewer have green bars beneath counts, like in the image below:

It may look as the bars are proportional to counts with 100% equal to the max count in the column. However, it's not the case. If you look closely at the screenshot above you can notice that values 02 and 08 have full bars (i.e. 100%) but their counts are not equal (2,236 and 2,922 respectively). More than that -- when no filter applied, all bars are at 100% regardless of the counts. OK, what do they mean then?

Each bar shows the selection ratio for each value and its value is calculated as count for current selection / total count of that particular value.

The best way to understand the bars is to think of them as of an inclusion indicator. They show the degree to which the current selection includes particular value: 0 means current selection doesn't include the value at all, while 100% means that current selection includes all rows with the value. That's why when no filters applied all bars are at 100%. For instance if dataset has field "Gender" with two possible values: "Male" and "Female", the bars would show to what degree the current selection includes males and females present in the dataset.

QViewer ver.3.3 is out

QViewer version 3.3 is now available for downloading. Here is what's new and exciting in the new version:

Support for QVD files larger than 2GB
Now you can open QVD files basically of any size. It was tested on QVD files up to 20GBs. Technically, at this point the only limitation is 2 billion rows per file. QViewer aggressively parallelize calculations so you may want to give it some time to perform necessary counts after opening a large file, because during that time CPU utilization can be near 100% which would make the application less responsive.

New Partial Load workflow
In the new version the workflow for partial load was significantly changed. The Partial Load button has been removed. Instead, when a file larger than certain threshold is opened QViewer suggests  performing a partial load (see the screenshot below). The threshold can be set at 512MB, 1GB, 2GB, 4GB or 8GB.

Indication of metrics affected by partial load
When a partial load is performed some metrics calculated by QViewer can be distorted because the loaded dataset is incomplete. To avoid confusion QViewer now shows which metrics are affected by partial load (shown red) and which are not.

Comments in generated script
When generating a LOAD statement, QViewer can now insert field comments obtained from the XML header. These comments are typically created using COMMENT FIELD or COMMENT TABLE statements in QlikView / Qlik Sense before exporting a table into QVD file.

Download QViewer

UPDATE 4/23/2017
I'm considering further improvements to QViewer. The ideas are floating around a few topics: table inspection for Qlik Sense Server, [shareable] selection bookmarks, support for other file formats (e.g. QVX, CSV, or XLSX), aggregation with built-in pivot tables (with selections applied). However, I'm not sure what would be the most useful for the Qlik dev community. I would appreciate hearing it from you -- what would you like to see in future versions of QViewer? Feel free to send me your suggestions (see my email in the upper right corner of this blog), or just leave a comment below.

Unplugged: What I learned about people and technology while writing QViewer

This post is not about BI technology or vendors or data analysis. "Unplugged" is a new kind of articles in this blog. It's my personal observations with a bit of philosophical contemplation, if you will. Today I'm writing about what I've learned while developing and selling QViewer -- a side project started as a "quick and dirty" tool for personal use which then became an interesting business experiment that taught me a few new things about people and technology:

You can make and sell things as your side project. It's doable. I remember that somewhat awkward feeling when I received first ever payment for software that I made myself. It was very unusual. I had experience of selling enterprise software with a 6-digit price tag, but that was someone else's business. Getting your own first sale in a $50 range was no less exciting if not more.

People in general are good. Once you start selling software you interact with people all over the world. And it turns out that people are generally good around the globe. I was surprised how many very grateful and positive people there is. Probably it's the most unexpected and gratifying outcome of the whole project.

Some people cheat with licenses. Despite the fact that the cheapest QViewer license costs less than a dinner for two, and, unlike the dinner, is acquired forever -- they still cheat. I understand it's a part of the human nature -- feeling frustration and pity when someone steals from you and at the same time enjoying the benefits of stealing from someone else even if it's just pocket money. People are complicated animals. So I'm not saying anything about the people that cheat. I'm deeply content that the majority is honest. The humanity definitely has a chance to survive :)

Some people are strange. No need to deal with them. After all, doing business is a two-way street. I remember one person demanded a sales presentation, a webex demonstration and sending him a detailed commercial proposal for QViewer because he and "his guys" wanted to evaluate whether it's worth spending couple hundred dollars. I replied that I'm ready to answer any specific questions, and offered to try the free QViewer to get an idea about the product. I've never heard from him again.

95% of technical support time is spent on 5% of customers. Some people are just like that -- they don't read instructions, forget things, don't check spam folders before complaining that their key didn't arrive, can't figure out what instance of QViewer they're launching, etc. It's OK, they just need more help. After all, adults are just grown up kids.

User recommendations is the best advertisement. So far I've spent exactly $0 for advertising QViewer. Yet, it's quite popular, mostly because of user recommendations. For me it was a good example of what it looks like when you made something useful. If people recommend it to each other -- you're on the right path.

1 out 10 orders never paid. Spontaneous decisions, no problem.

Payment reminders work. Sometimes, your invoice sent to a customer may be buried in his/her email box under a pile of unread messages. Sending a friendly reminder once might help. Just once, that's enough for those who are really looking to buy.

Even small side projects can be extremely good for career opportunities. Needless to say, mentioning QViewer in my CV helped me tremendously in finding new employers (when I looked for them). I would argue that the salary increase it enabled has earned me more than selling QViewer licenses alone.

Developer tools are amazing nowadays. I wrote my first program in 1986. It was in BASIC on a military-grade DEC-type computer. In 90s I wrote programs in C++, Pascal and Assembly. Between 1998 and 2011 I didn't write a single line of code (except some Excel macros). Boy, how things have changed since then. When I started writing QViewer in 2012 I was totally fascinated with the capabilities of Visual Studio and C#. Later I fell in love with F# but that's a different story. And thanks God we have StackOverflow. Writing software has never been easier.

Obfuscate and protect your software. Sooner or later someone will try to disassemble your software for a purpose that might be disappointing for you. There is no absolute protection, but raising the barrier can significantly complicate the task. Once I interviewed a developer for EasyMorph. Trying to impress me, the guy told me that he also wrote a QVD viewer. However, after not answering a few questions about the QVD format he quickly admitted that he just disassembled and repacked some components of QViewer. I learned a lesson that day.

Writing and selling software changed my perception of the software industry. I understood what it takes to create it. I stopped using any pirated programs. Now I use only licensed software, even if it's rather expensive (I'm looking at you, Microsoft), and I always donate when a program is available for free but donations are accepted.

How to use the memstat metrics in QViewer

Starting from version 3.1 QViewer shows two memstat metrics in Table Metadata window:

• Size, bytes -- total size of the column in bytes
• Avg.bytes per symbol -- average size of column values in byte 

click to zoom

These metrics are calculated similarly to memstat data available in QlikView (not available in Qlik Sense so far). Since the structure of QVD files is very close to the internal in-memory data format in Qlik these metrics can be used to optimize (reduce) memory footprint of resident tables which can be desirable for particularly large applications. The most convenient way to inspect resident tables in QViewer is setup a simple generic subroutine as described here. Alternatively, you can insert temporary STORE statements to save resident tables into QVDs and then open them in QViewer manually.

When looking at the memstat metrics in QViewer you would typically want to identify columns that take most of space (hint: click column headers in Table Metadata to sort the grid). A few things that you can do reduce table size:

• Remove unnecessary columns that take a lot of space
• Force Qlik to convert duals to numbers by multiplying them by 1
• Trim text values to remove trailing and leading spaces
• Use integers instead of floats where possible
• Round up floats to fewer decimal digits to have fewer distinct values in the column
• Use autonumbers instead of long composite text keys

Read also "A few tips for dealing with large QlikView applications".

Remarks
Qlik uses special format for columns with incrementally increasing integers (autonumbers) -- they basically don't  take up any space in memory, although QVDs store them as regular columns.

The memstat metrics are calculated correctly even if QVDs are partially loaded in QViewer. Therefore you can use them in the free version of QViewer, or when partial loading was used.

QVDs generated in Qlik Sense v.2 and above are compatible with QVDs generated in QlikView and therefore can be opened in QViewer as well.

The total size is NOT simply the avg. symbol size multiplied by # of rows -- it's calculated using a more complicated logic that accounts data compression.

QViewer v3: Qlik-style filtering and full table phonetic search

New major version of QViewer is out. You can download it here: http://easyqlik.com/download.html

Here is what's new and exciting about it:

Qlik-style filtering

 

click to zoom

The headline feature of the new version is the ability to filter tables in the Qlik fashion using listboxes. Applied filters can be seen in the filter bar that appears above the table (see screenshot above).

When a selection is made, value counters update automatically. The green bar charts behind the counters hint at current selection count vs total count ratio.

Note that nulls are also selectable.

Full table search

It is now possible to search for a value in entire table. Current selection becomes limited to only rows where the searched value is found. Full table search is basically another kind of filter. When it's applied, it can be seen in the filter bar.

Phonetic search mode

The full-table search and the listboxes allow looking up values by matching them phonetically. This is helpful when you don't know for sure the spelling of a word in question. For instance, if you search for "Acord" values "Accord", "Akord", "Akkort" will match. Phonetic search works for whole words only. Currently, only English phonetic matching is supported.

Cell metadata

 

You can view additional metadata of a value in question using Cell Metadata floating window. This is particularly helpful for easy detection of leading and trailing spaces in text values.

New license key format

The new version requires upgrading license keys. All license keys purchased after 1st of August, 2015 will be upgraded for free. License keys purchased prior to that date are upgraded at 50% of the current regular price. To upgrade a license key please send me your old key and indicate the email address it is linked to.

Hints and tips on using QViewer for inspecting resident tables in QlikView

Three years ago I wrote "How to look inside resident tables at any point of loading script". This technique proved to be quite successful and efficient, and was praised by many prominent QlikView developers since then.

This post is a round-up of some best practices of using QViewer for inspecting resident tables in QlikView, collected over the last 3 years:

Here is the most recent variant of the INSPECT subroutine:

SUB Inspect (T)
    // let's add some fault tolerance
    LET NR = NoOfRows('$(T)');
    IF len('$(NR)')>0 THEN
        // Table exists, let's view it
        STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd);
        EXECUTE "C:\<pathToQViewer>\QViewer.exe" "$(QvWorkPath)\~$(T).qvd";
        EXECUTE cmd.exe /c del /q "$(QvWorkPath)\~$(T).qvd";
    ELSE
        //Table doesn't exist. Let's display a messagebox with a warning
        _MsgBox:
        LOAD MsgBox('Table $(T) doesn' & chr(39) & 't exist. Nothing to inspect.', 'Warning', 'OK', 'ICONEXCLAMATION') as X AutoGenerate 1;
        Drop Table _MsgBox;
    ENDIF
    // Namespace cleanup
    SET NR=;
ENDSUB

Installer of the next version of QViewer will be creating a registry key with path to QViewer, so the subroutine will be able to use the registry key to get location of qviewer.exe instead of hardcoded file path (kudos to Matthew Fryer for the suggestion).

INSPECT is quite helpful in verifying joins for correctness. For this, insert CALL INSPECT twice -- once before a join, and once after it. This will allow you to see whether the resulting table has more rows after the join than before, and check if the join actually appended anything, i.e. if appended columns actually have some data in them.

To find duplicates in a column -- double-click the column header for a listbox with unique values in that column, and then click Count in that list. On first click QViewer will sort values in descending order thus showing duplicate entries (which have counts > 1) at the top of the list. Checking a primary key for duplicates after a join can help detecting wrong joins.

To find duplicate rows in a table -- click "Morph It" to open the table in EasyMorph, and then apply "Keep Duplicates" transformation. You can also filter rows, if you apply "Filter" or "Filter by expression" transformation.

When you deal with wide tables that have many columns, you might need to find specific column. Press F5 to open Table Metadata, and then sort field names in alphabetical order. Another common use case for Table Metadata is checking whether columns have values of expected type. For instance if a column is expected to have only numeric values, its "Text count" should be 0.

To find a value in a column -- double-click the column header to open a list of unique values, then use the search field above the list. To locate the searched value in the main table, simply double-click the value in the list. Press F3 to find next match in the main table.

Currently, the search feature is somewhat obscured (as rightfully pointed by some users). We will be introducing a more convenient full table search in QViewer v2.3 coming out in June. Subscribe to our mailing list on easyqlik.com to get a notification when it happens.

Major redesign of QViewer

It's been a while since I posted last time and now I've several posts and announcements waiting to be published.

Today's post is about major QViewer redesign and update which has been due for a long time. I wrote first version of QViewer 2 years ago mainly for myself, although it was interesting to see if it can be useful for other QlikView developers. I turned out -- it can, and in the last 2 years it was downloaded more than 10'000 times (7'500 downloads of 64-bit version and 2'500 downloads of 32-bit version) with average number of downloads steadily going up.

At the same time the utility remained to great extent what it was initially -- a custom-made "home-grown" software written during afterhours and in spare time, and it had good potential for optimization.

Today, I'm releasing deeply updated and redesigned QViewer version 2.0 which is finally made comme il faut, i.e. -- as it should be. If you used QViewer previously you will notice new look and feel of the new version, however the majority of changes are under the hood. During the redesign the main focus was on performance and memory consumption that would allow using QViewer with large QVD files (up to 2GBs and more) comfortably. As the result, memory consumption was reduced in 5-7 times, and load speed has reduced in 5-10 times. For instance, load time for a sample 200MB file which I'm using for internal testing, has decreased from 17 seconds to just 1 second. Even more than that -- now load time depends only on hard drive speed, so if you have SSD -- you will enjoy even faster opening.

Two other changes -- more responsive user interface better suited for larger displays, and replacement of Query (simple aggregation feature) with integration with another tool which will be released soon. Also the free version now allows twice more rows -- 100'000.

Your existing license keys will keep working with version 2.0 so no replacement needed.

Don't hesitate to try out new QViewer and let me know what you think about the changes.

FastStart: tiny package to speed up creating new QlikView apps

FastStart is a tiny bundle consisting of an empty QlikView application with a couple of useful subroutines packed with empty configuration .xls file and portable QViewer for inspecting resident tables. I made it for myself for the cases when I need to quickly create a new app from scratch but you're welcome to download and use it. It was made suitable for rapid debugging and dealing with large datasets.

I'm sure there are other similar bundles, here is my approach:

Changes to default Document Settings:

• Compression -- None
• Generate Logfile -- Yes
• Default font -- Calibri, 9pt

Changes to default Load Script:

• Added Subroutines tab that contains two subroutines -- LoadVars (loads variables from external configuration file) and Inspect (calls QViewer for inspecting contents of a resident table);
• Added Partial Load tab -- calls LoadVars so that variables can be updated without full reload (by simply pressing Ctrl + Shift + R), also can hold additional script commands for execution during Partial Reload which is convenient for modifying data model;
• Default tab Main renamed to Variables  -- intended for setting up variables and runtime parameters;
• Added Main tab -- blank tab, here we start manipulating with data.

Two additional folders

• Config -- contains config.xlsx
• Tools -- contains QViewer.exe

I intentionally tried to keep it minimalist and not overload it with pre-built stuff like UI layout framework, calendar scripts, predefined color variables, etc. Although, external configuration file for storing variables and subroutine for inspecting resident tables -- these two features I use in almost every application.

Better Lineage in QViewer and some other updates

Here is a brief overview of features recently added to EasyQlik QViewer -- my favorite QVD viewer :) If you're subscribed to QViewer newsletter then you've heard about some of them except the most recent.

Better Lineage

Since QlikView 11 all QVD files generated by version 11 apps contain SQL statements from loading script. This is quite handy when you need to recall what fields from database were used as source for a QVD.  It doesn't contain full snippets of script so it's not possible to see entire transformation logic, however this is handy when you only need to remind yourself what it was. Also QVDs contain connection strings, so that you can double-check if source database and schema are correct. Previously, Metadata window in QViewer showed only 1 line per Discriminator/Statement and it wasn't convenient for viewing SQL statements. Therefore starting from version 1.4.4 there is new additional multi-line window for viewing Discriminator/Statement records from QVD headers. See screenshot below (clickable).

QViewer rebranded, got paid version

A few significant changes happened to QViewer -- viewer for QVD files which I wrote a few months ago and still continue developing.

First of all, QViewer has been re-branded as EasyQlik QViewer and got its own web-site http://easyqlik.com (which however doesn't differ much from the old one).

Second major change is that QViewer has now two versions -- free and full. Free version is limited to 10'000 rows which is still quite enough to do some data profiling. Full version is paid (license key can be purchased for $45) and unlimited. License is personal (i.e. limited to a person, not organization) and has no limitation by number of installations -- you can use QViewer on as much computers as you need. In 8 months the demo version was download almost 1'000 times so it gives me the hope that someone found it as much as useful as I do :) However, further development of QViewer in terms of features, usability and functionality will directly depend on number of licenses purchased as this will show if QViewer is really useful tool or not.

Third change is that finally I got rid of troublesome ClickOnce installer which caused lots of problems with installation and even more with uninstallation. Now QViewer comes with standard neat NSIS-based installer and actually is a portable application. It means that you don't necessarily need to run installer if you want to move QViewer to a different machine. Once it has been installed, you can simply copy qviewer.exe to other machine or add it to your portable apps collection on USB-stick. However, running installer has advantage of automated association with .qvd file extension, which can be problematic otherwise. And as previously, QViewer requires installed .NET v4.0 or above -- keep this in mind when using QViewer in portable mode.

Future development plans include adding sort functionality for listboxes and metadata tables, and design of custom data grid. The latter should significantly decrease load times as the current implementation uses standard DataGridView which is so slow that only initializing it actually takes major part of total load time.

And of course, if you have feature suggestions -- don't hesitate to give me a shout by email or in comments here.

How to look inside resident tables at any point of loading script

What do you usually do when you need to look inside a resident table somewhere in the middle of loading script? I guess you store it as QVD, then load this QVD in a separate temporary QlikView application, then create table box or something else to browse the table.

Here is small trick how you can make your life easier using QViewer:

Insert in the beginning of the loading script this short snippet:

SUB INSPECT (T)
LET vPathToQviewer = 'C:\User\Dmitry\AppData\Local\EasyQlik\QViewer\';
STORE $(T) into [$(vPathToQviewer)temp_qvd.qvd] (qvd);
EXECUTE "$(vPathToQviewer)qviewer.exe" "$(vPathToQviewer)temp_qvd.qvd";
ENDSUB

And then simply insert calls to this function in any place of the loading script where you need to inspect table in its current state. For instance:

CALL Inspect('Orders');

When execution of the loading script will come to this line it will open QViewer showing the table. Script will continue running as soon as you close QViewer.

This is convenient technique that allows inspecting resident tables after every transformation like joins, concatenate loads, etc. You can have as many inspection points as you need during a single run.

A few more tips:

You may receive Security Alert from QlikView because of EXECUTE statement as depicted below. Just press Override Security button. Or you can change security settings in the bottom of the Script Editor window.

You can put this snipped into some file (e.g. debug.qvs) and then include this file when you need to do debugging of some application. Also you might want to save inspected tables under their own names in some temporary folder -- just modify the snippet accordingly or add a new similar function, e.g. InspectSave.

Any more ideas?

UPDATE 1

+Donald Hutchins made a good point -- he offered to store temporary QVD file not in QViewer's folder but in the working folder of the appliction which loading script is being executed. I think it makes a lot of sense because of less problems with access rights and better fit for parallel use. Here is his variant with my minor changes:

SUB INSPECT (T)
    STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd);
    EXECUTE "C:\.......\QViewer.exe" "$(QvWorkPath)\~$(T).qvd";
    EXECUTE cmd /c del "$(QvWorkPath)\~$(T).qvd";
ENDSUB

UPDATE 2

+Matthew Fryer proposes one more variant of the Inspect sub in his blog QlikView Addict.


UPDATE 3

You can use EasyMorph for exactly the same purpose -- inspecting resident tables. Pros: with EasyMorph you can do more than just viewing a QVD -- you can filter, aggregate, calculate new columns, join other tables, etc. Also the free EasyMorph doesn't have the limit of 100K rows as the free QViewer does. Cons: EasyMorph opens QVD slower than QViewer as it needs to convert them to its internal format. Also it's less memory efficient when it comes to large QVDs.

The only difference in script would be additional /load key:

SUB INSPECT (T)
    STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd);
    EXECUTE "C:\.......\morph.exe" /load "$(QvWorkPath)\~$(T).qvd";
    EXECUTE cmd /c del "$(QvWorkPath)\~$(T).qvd";
ENDSUB

QViewer: What's next

Two weeks ago I made initial version of QViewer (my standalone QVD file viewer) available for public download. Since that time it was downloaded more than 300 times. As I myself am a practicing QlikView developer I tried to build a tool which I would use in my daily work. Therefore, a few more features were added. E.g.

• Partial load (for dealing with large files)
• Search
• Pre-calculated statistical data (various counts for each field and each unique value)
• Query tool for calculating simple aggregates (counts, sum, avg)

They make QViewer not simply a file viewer, but actually a data profiling tool which should help find data anomalies (unexpected nulls, text instead of numbers, duplicate rows, etc.) faster. I tried to automate some frequent analysis operations which usually require some time & effort when using regular QlikView. For instance, besides viewing QVD row-level data, with QViewer you can do a little bit faster the following:

• Search a field for nulls
• Filter values by type (text/number)
• Count unique values
• Count occurrences of each unique value
• See immediately type of value (numeric/text/null)

These operations are pretty basic and make no problem for regular QlikView developer, but they take time because of their frequency. If QViewer can save you 15 minutes a day, it saves you 65 hours of working time a year (or more than 8 full working days). Multiply this number by your hourly rate and see the benefit in cash equivalence :)

Digging QVD format was an interesting exercise -- since it's a native QlikView format it gives some understanding how QlikView works under the hood, from what it started and how it has evolved into what we know today. I now understand better technical challenges with which QlikTech's developers faced and I have to admit that they've done excellent job resolving them. Exceptional QlikView's performance, which we know, is result of smart optimization and hard work. Also it looks like there was a portion of luck as some QlikView's key features were not designed as those from the beginning, but were result of evolution.

What's next for QViewer? As of today, I've implemented all essential features which I planned to make it useful for daily work. It will be remaining free until end of this year. Before end of this year a paid version to be introduced along with free limited version.

There still are some things to do, mainly because QViewer still doesn't perform as I want it to on large files due to limitations of standard .NET control (specifically DataGridView). I'm thinking about writing own custom control to replace it but this task may be very time-consuming. It would also be interesting to implement partial load with WHERE condition, as well as make Query a bit more advanced and, for instance, make it support conditions like where field is null.

I would appreciate to hear your ideas -- how to make QViewer better for your daily work. Please, leave your comments here. Thanks.

Explainum QViewer - viewer for QVD files

I've developed a viewer for QlikView's QVD files -- Explainum QViewer. Associate it with .qvd file extension and view QVD files in one click.

It can be downloaded here for free.

Screenshot (click to enlarge)

UPDATE
The tool has been rebranded as EasyQlik QViewer.