LivingQlik Roots: How-To Create Cyclic and Drill-Down Grouped Dimensions

Featured Image - Cyclic and Drill-Down Grouped Dimensions

Today, I will cover some basics on how to create dimension groups for your QlikView applications. I will also talk about using GetCurrentField to make your charts behave more seamlessly with your groups. This info is must-have for any serious QlikView developer.

What are Grouped Dimensions?

When you build a chart, you typically will use at least one dimension field so that the aggregation in your expression gets calculated for each value in your dimension list. This is a simple concept that we can expand on.

Grouped Dimensions allow the user to “switch out” the dimension with another field. This allows us to re-use the screen “real-estate” without having to create a separate chart for every needed dimension.

Grouped dimensions can be used in any chart. I also use them sometimes in listboxes. They save space if the users understand how they function.

Cyclic vs. Drill-Down Dimensions

In QlikView, grouped dimensions come in two flavors.

Cyclic Dimension – Allows user to choose the dimension at will.

Drill-Down – When a user makes selections resulting in one possible value, the dimension changes to the next dimension field.

The key thing to remember here is that users directly control the dimension in the cyclic style, while the selections drive the displayed dimension in the drill-down style.

Creating a Cyclic Dimension Group

You should know that when you create a dimension group, it can be used in any QlikView object within your application. With that said, they can be either created within your Document Properties or directly in a chart. Go to Settings –> Document Properties –> Groups and select New

Create a group name, choose the radio button for Cyclic Group and then add the fields you want in your group. Note that the order of these fields will represent the order in which users can normally switch between them with the top field being default.

You can also change the label for each field. Simply click on the field and enter a new value in the label field. In this group I renamed my field MonthYear_Short to Month.

Now create a chart using your new cycle group as a dimension. You will see grouped fields at the very top of the dimensions tab.  Once your chart is created, the user can click directly on the yellow circle or can use the pull down to select the desired dimension. Super Cool!

Creating a Drill-Down Dimension Group

Now let’s create a drill-down dimension. This time we will build the group from the chart. Create a line chart and when you get to the dimensions tab, you can select Groups in the bottom left.

Name your group and select the Drill-down radio button. Note that the fields I add are in a specific order from summary level to detail. The fields must be in a hierarchy because of the way the drill-down field operates.

Utilize the new drill-down group as your chart dimension and finish creating your chart. Note that clicking the arrow does nothing at the highest level. Instead, make a single selection in your chart and you will see the dimension change to the next level. At that point you could click the arrow which serves to undo that selection. As you make selections in these fields or other fields that result in only one possible value, your chart will automatically display the next detail level.

Which Group Type is Better?

I am often asked which style is better and my answer is always “it depends on your audience”. Most sophisticated users will prefer a cyclic group because they can directly control the dimension being displayed regardless of the selections.

Users who “just need the dashboard to work” will probably prefer the drill-down groups as they don’t have to do anything to the chart to make it drill down to the next level.

With that said, I have had situations where the client wanted the same chart done both ways.

Professional Tip – Sorting

You might run into a situation where a field in your group is not sorting correctly when in use. Take the example below where we have used Quarter Year. Sorting this alphabetically is wrong and numerically does not work either. Either way, we cannot determine the sort from our normal chart Sort tab.

Open the group and go to Sort Orders. There you can select the field and assign the sort of your choice. In this case we are sorting by expression using a counter field that was added to the data model for this purpose.

This is Where GetCurrentField Comes In

Sometimes, an expression you create needs to interact with the fields you are using for dimensions. This is typical for expressions with aggr functions. So what do you do when your dimension can change? Well, of course, there is a function for that. Let’s cover a few applications of GetCurrentField.

One great application of GetCurrentField is to provide some clarity regarding the dimension currently in use for the chart. I like to modify the title of the chart so it displays the field that is currently being used. As the user cycles or drills though the dimensions, the title of the chart changes to match.

This expression could be entered in the General tab for Window Title.

Resulting in a dynamic title that changes with the drill down.

In another use-case, we have created a line chart that measures profit over time. And instead of using a static dimension like date, I instead created a cyclic group that allowed the user to cycle from Year, to Month, to Date. This is a very common requirement. But in addition, the users require a reference line 75% to the maximum value of the chart and also at the 25% point. This would be pretty simple but how do we know what the highest value is as we cycle from one dimension to another.

Reference lines are created in the presentation tab. Notice that I had to put the GetCurrentField function in dollar-sign-expansion.

Note: If GetCurrentField is not working try putting it in dollar sign expansion

I created both lines and here is the final chart. As I click the cycle button, the reference lines recalculate to the new maximum value.

Final Thoughts

Please note that Qlik Sense does offer some concept of grouping fields but operates a bit differently. And to-date, GetCurrentField is NOT a supported function.

Here is some interesting further reading on the GetCurrentField function.

More Reading –

QlikView Maven – Expression Knows Which Cycle Group Field is Active

Quick Intelligence – QlikView Caption Contest

QlikShare – System/Meta Data Functions in QlikView – Part 1

Happy Qliking!

5 comments

The 9 Worst Data Visualizations Ever Created

Every year, the worst movies of the year coming out of Hollywood are “honored” with an award called the Razzie. In an industry that normally pats itself on the back at every turn, the Razzies are a nice way to recognize that not every film churned out of the Hollywood machine is worthy of praise.

In similar fashion, I thought it would be fun to award some of the worst data visualizations coming out of our collective BI industry. Although it is always fun to poke fun at data visualizations that might be lacking in usefulness, it is also an opportunity for us to learn so that we do not make the same mistakes in our own work.

Not Using Dimension Limits

This was an amazingly inept example I came across. This pie chart actually breaks a few rules.

First, a pie chart is supposed to show relationship of each slice to a whole. Sampling only the 100 most active tweeters and making that the whole in this case does not really give us real value. A simple bar chart would have been a fairer representation.

Secondly, and more obviously, how does representing all 100 slices help us? We certainly cannot see all 100 names in the legend on the right, not can we detect the differences between the slice sizes as we progress along the slices.

3D Pie Charts

Speaking of pie charts, here is another one that definitely “chaps my hide”. This pie chart is already bad just because it is in 3D. Tilting the pie to give it a 3D appearance distorts the slices, making it harder to detect how large the slices are relative to each other and to the whole.

Add to that, the slices have a high degree of transparency to make it even more difficult to see where a slice begins and ends as the colors bleed together through the depth of the chart.

Not to pile-on, but the developer also neglected to sort the slices in descending order by the metric, making this even harder to read.

Speaking of reading, forget about figuring out which label goes with which slice. It is not possible to follow the little lines to get to the correct label. Ouch…

Alternative Facts

I couldn’t resist adding this one. It only has 3 slices which surprisingly add up 193%.

3D Bubble Chart

I can admire this developer’s ingenuity. “If only the scatter plot could handle one more expression”. Making the chart 3D indeed adds another axis the developer calls the S-axis. But trying to make sense of this takes a whole lot of effort from the reader.

Also, because the chart is presented in 3 (oh wait, 4) dimensions, it is difficult to decipher if a sphere is larger because it is close to us on the Z-axis or if it is just larger, corresponding the S-axis.

Line Chart on a Non-Continuous Axis

The chart below is an example of using the wrong chart type for a data visualization. This data would have better been presented with a bar chart.

Line charts are meant to display an expression against a dimension list that is continuous, where the values relate to each other in a specific order. I generally think of a date sequence like months or years.

The card games presented here could be in any order. There is no reason to connect the points with a line.

The 3D Bar Chart

I have a special hatred for the 3D bar chart. Firstly, by nature of the chart being drawn in 3 dimensions, it is difficult to follow the height of each bar to the correct Y-value. This becomes more difficult as we get away from the axis.

But the more serious crime is that I can’t even see some of the values for the rear dimensions as they are hidden by the bars of the values in front of them. I guess those data points were not important.

The Tip of the Iceberg

Speaking of Fox News, here is another winner. This time we are looking at a bar chart representing people who have enrolled in United States government sponsored healthcare. If you do not inspect the numbers (which were thankfully printed on the chart) the bar on the right appears to pass the bar on left 3-fold. But if you take the numbers into account, it becomes more obvious that there is something wrong here. There should only be about a 15% difference from one bar to the other. Why is the visual so far off?

Well we don’t really know, but my assumption is that the creator of this chart decided to start the Y-axis at a number other than zero. We don’t really know where the Y-axis really starts here because there are no numbers on the axis. This is a very misleading chart.

The Dreaded Infographic

Hold on to your seats. This one is really bad. In this infographic of “How Baby Boomers Describe Themselves”, there are so many problems, that I don’t know where to begin.

Let’s start off by saying that the percentages given do not add up to 100%. Does this mean that the data was incorrectly calculated? Or does it mean that each person was allowed to describe themselves with more than one trait? We will never know.

Also, no matter how you look at the color areas of the chart, the area or vertical space of the colors does not seem to correspond with the numbers presented on the right.

Having the colors filling up the shape of a person does not help the situation. Because a novel shape will have varying widths from bottom to top. This makes it very hard to know what percentage each color represents.

Finally, what is the value for this particular data set of having the shape be a walking person?

As a general rule, maybe we shouldn’t use a strange shape to represent these values. It only serves to confuse and obscure the story the data is trying to tell.

The Worst of the Worst

So what is worse than using an obscure shape to represent your values? I would submit that using a visual shape that is in itself, a whole other kind of chart would be worse.

At first blush, this data appears to be related to the 50 United States. But the data actually has nothing to do with geographical analysis. The reader is supposed to read the chart from left to right as the west coast corresponding to the year 1960 and time moving forward to the east coast which represents 2060.

The second problem with this chart is that none of the percentage seem to add up to 100%. For the left and right extremes we can maybe assume that the numbers for the upper regions are simply too small to be displayed. But how do we explain the middle section? There are only three colors and the three numbers add up to 92%.

This chart should be a stacked line chart. That way we could clearly see the important parts of the chart where the lines experience real movement. Ironically, what could be the most important part of the chart (the great lakes area in the northeast) is completely missing.

Final Thoughts

The mistakes in these date visualizations are obvious and extreme. But after you point your finger and chuckle, take a step back and look at your own visualizations. I know I have made some of these mistakes on a smaller scale.

Happy Qliking!

2 comments

LivingQlik Roots: The Ultimate QlikView Set Analysis Reference

QlikView Set Analysis Ultimate Reference Living Qlik Featured Image

QlikView Set Analysis – A Scary Subject

Set Analysis is a scary subject. I would surely claim it is the most difficult part of developing your QlikView front-end.

I have waited to throw my hat in the ring regarding set analysis. This is mostly because I didn’t feel I had much to add, but also because Set Analysis in QlikView is a formidable subject.

But the time has come for LivingQlik to cover Set Analysis. Firstly, no self-respecting blog would be complete without tackling the subject. Secondly, I feel that there is room for a compete reference presented in a clear voice. Can LivingQlik be that voice? Maybe?

This is the ultimate cheat sheet for Set Analysis in QlikView. It includes the syntax definitions and options, many examples and tons of tips and tricks to make the most out of this complex topic.

One thing I tell students is to not beat themselves up if they don’t absorb everything there is to know about Set Analysis in one sitting. It is a difficult subject for all skill levels, from newbie to experienced. I know I still run into situations that make me scratch my head and I have been doing this for a while.

What is Set Analysis?

The purpose of Set Analysis is simple.

Set Analysis enables a Qlik aggregation expression to be calculated with a set of selections that differ from the user’s selected values.

When we use an expression with set analysis inside a chart, the chart will be calculated based on the data set within the expression rather than the green selected values and white associated values that would normally filter the chart.

This line chart shows Quebec sales even when Quebec is not selected

Comparing points-in-time would be difficult without set analysis.

Here we look at a month vs the same month last year.

Looking at Sub-Categories that begin with the word “Office”

How Does Set Analysis Work?

Admittedly, the syntax for QlikView Set Analysis is not easy. Here, I will lay it out as plainly as possible. Where there are several options, I have attempted to highlight the more common use-cases.

Set Analysis is always enclosed in curly braces and must exist within an aggregation like sum(), avg(), max(), min(), or only(), for example. In fact, Set Analysis is the only place where curly brackets are utilized in Qlik syntax.

Sum up the sales for all data excluding the current selections limited to the Quebec Region.

The syntax for Set Analysis is broken out kind of like grammar rules break out a sentence. Some say there are 3 parts but I detail 4 (Element List is really part of the modifier but this is where much of the “action” takes place):

  • Identifier – the selection state established for your set
  • Operator – Method for combining two or more identifier-modifier sets together
  • Modifier – Assigns specific field values to your set expression
  • Element List – Within the modifier, the specific selections assigned to your modifier field

 

Identifier – the selection state established for your set

An identifier is an optional prefix that determines the starting selection state of our set. Identifiers get applied to the modifiers they precede.

The default identifier is the dollar-sign {$} which tells the expression to respect and apply the user’s current selections in the default state before evaluating the set. Because developers are usually using the default state of the data model, developers often leave the dollar sign out since we are already in the default state. If your object or sheet is responding to another state, and you actually want the expression to reference the default state, then the dollar-sign would be needed. Omitting the identifier just means we are responding to the state of the object or sheet following normal inheritance rules.

 

 $ 
 Respects user selections in the default state of the object 

 1 
 All the data (ignores all user selections) 

BookmarkID
The selections that would be invoked by a bookmark

Alternate State ID
The selections from an alternate document state

$1
Previous Selection (like hitting back) (ignores all user selections)

$2
2nd previous selection

$_1 
Forward Selection

$_2
2nd forward Selection

0
Empty Set

 

Average of sales for all data in the application (ignore all user selections).

 

When working in the default state we often omit the $ as an operator as it is redundant. But be careful about omitting the identifier when working with other states.

Operator – Method for combining two or more identifier-modifier sets together

Operators are used to join two identifier-modifier sets. Operators can also be used inside a modifier element (more on this in the Element List section). There are only 4 variations.

Operator
Definition

+
unions two sets together

exclusion of second set from the first

 * 
 intersection of two sets 

/
symmetric difference (values that belong to either but not both sets)

 

Identifiers and operators can be used to create some very interesting sets that might (or might not) have value to you. I find that I use the intersection operator more often than any of the others, but in all reality, the sets I require generally do not require an operator.

Sum of profit for Region Ontario and Product Category Furniture but also including profit for Region Nunavut and Product Category Technology.

Sum of profit for all the data in the app excluding the current user selections.

Sum of profit for last year but only for the period less than today one year ago. This is profit LYTD.

Sum of profit for Chris and Erin or for Year 2017, but not for Chris and Erin in 2017.

Modifiers – Assigns specific field values to your set expression

A modifier gives you a way to assign specific values to fields when determining your set. The modifier is always enclosed by angle brackets: <>. Here you will list field names, each followed by an equal sign and then an element list. The modifier is where things start to get interesting.

Sum of sales for Quebec Region.

Within a modifier you can:

…assign values to multiple fields separated by commas:

Sum of sales for Ship Mode Express Air in Region Prarie.

…assign a field to the same field in another state

Sum of sales transferring the Manager selections in Group1 to the default state.

Element Lists – The specific selections assigned to your modifier field

The portion of the modifier after the equal sign is called the element list. Values listed or evaluated here become specific selections for your set evaluation. The element list is surrounded by curly braces and should not be confused as an inside or nested set. We can do many awesome things with the element set and it is often where I spend the most time when designing Set Analysis expressions.

Within an element list you can:

…assign more than one value to a field separated by commas

Sum of sales for Ship Modes Express Air and Regular Air.

…utilize wildcard searches

Sum of sales where product name begins with “Avery”.

For element values, it is best to use single quotes to identify a string although if the text does not contain spaces or other special characters, you can get away with not using them. When expecting numeric values, no quotes are necessary. Be advised that date fields can be tricky and will conditionally require quotes based on the evaluation and format of the field.

A field used inside a modifier overwrites the user’s selection for that field. Therefore we can do things like “ignore the user selections for this field”. But there is some nuance in how you should approach this. For example, the two following examples will treat the Manager field slightly differently and could result in different calculations:

Sum of sales respecting all user selections and selecting all possible (non-null) values for the field SalesPerson.

 

Sum of sales respecting all user selections ignoring user selections in the SalesPerson field.

Searching for the Element List Values

One way of developing your element list is to search for it. Enclose your advanced QlikView search syntax inside double quotes within your element list. This is especially helpful to limit your set to a date range for example.

Sum of sales for the last 30 days from the maximum current date selection.

Here we have closed the range.

Sum of sales for the last 30 days measured from yesterday backwards

You can define a calculation to search for values.

Avg of sales for the dimension value that ranks number 1

Or even assign a field to another field.

Sum of sales for Date_Field where the Date_Field is the same as the Ship Date (same day shipping)

P() and E() – Special Elements

The P() and E() special elements return the element set that is possible or excluded (respectively) from a field or expression. I don’t have requirements that call for the possible or excluded functions very often.

Sum of sales respecting current selections for the Managers that have ever sold Product Sub-Category Labels.

And an example with e():

Sum of sales respecting current selections for the Product Names that were not associated (not sold) last year.

Passing States into the Modifier

Another interesting technique involves alternate states you might have in your document. We have already seen the possibility of using these states as operators. But we can also use them in the modifier to pass the selections in a field from one state to another.

Sum sales in the current inhereted state passing in the selected values from the Group1 state.

Dynamic Elements – Dollar-Sign Expansion

Dollar-sign expansion is a frequently used feature of Set Analysis. Think of the example where you want to display sales for this year-to-date. I could just type whatever the current year is into the modifier element list, but then I would have to come back to this expression on January 1st and change the year.

Thankfully, you will not need to hard-code a year and then change to the next year every January. Using the concept of Dollar-Sign Expansion, you can dynamically assign the value to the modifier element.

Syntax inside the dollar-sign expansion will be evaluated once for the entire chart before the rest of the expression is calculated. This is usually great. But because of this, you cannot leverage set analysis to do row-by-row evaluations, expecting the set to change for each dimensional value. For those occasions, use an if statement.

Here are some examples of some point-in-time sets using dollar-sign-expansion. These are examples of how I do it, but there are many ways to “skin the cat” and different approaches have different advantages.

Sum of sales for the maximum year in the data.

Sum of sales for the maximum month in the data vs. the same month in the prior year.

Sum of sales for maximum year-to-date vs. the prior year-to-date.

Operators within Modifiers

The use of operators is not limited to the beginning of the set. You can also utilize modifiers within a modifier either as an assignment operator or inside an element list (You can ignore the syntax checker here)

Sum of profit for the currently selected Product Sub-Categories adding the selections Envelopes and Labels to this.

You can also assign the operator before the element list. This has the exact same result as the above example.

Sum of profit for the currently selected Product Sub-Categories adding the selections Envelopes and Labels to this list.

A word of caution here. If you need to use multiple operators within the modifier, put them all in the element list rather than utilizing both the element list and the assignment operator before the list. Using the second example below will not exclude Paper.

Another great example is the use of asterisk and equals. This has the effect of considering the user’s selection from the modifier field before calculating the set. One could argue this methodx of developing sets is actually more intuitive to the user than the “normal” approach of completely overwriting the user’s selection.

Sum of sales for Product Sub-Categories Envelopes and Office Furnishings, respecting the users selections within that field. 

Here we have the expression written with and without the asterisk operator so you can see the difference in behavior.

General Tips

You can use sets within other sets

When using nested aggregations with the aggr() function, it might be necessary to use the same set in more than one place in your expression

For QlikView, debugging your set can be done by putting your expression into a straight table and leaving the expression label blank. The dollar-sign expansion portions will evaluate in the header row. As a rule, I leave my set analysis labels blank until I have confirmed that the dollar-sign expansion is working and the expression is accurate overall.

Take care when using set analysis to label your expression appropriately. By definition, you are affecting the normal selection state of the calculation. Be sure you clearly tell the user how that expression is actually calculating. Incorporating the modifier value might be a good way to do that.x

You can use flags to identify month-to-date and other points in time to use in sets. But keep in mind these will not be dynamic (the date range evaluated will not change with date selections).

More Resources

Here are a few resources that can give you further reading.

The QlikView Set Analysis Cheat Sheet

A great cheat sheet style reference by the incomparable Miguel Ángel García.

 

Les Set Analysis

A wonderfully clear and thorough document by Fabrice Aunez that I suspect was translated from French.

 

Set Analysis Wizard for QlikView

A tool to generate set analysis expressions for your use. By Stefan Walther

Quick Intelligence – QlikView Set Analysis – Star Equals

A truly inspirational piece of knowledge from Steve Dark on the asterisk operator in a modifier.

 

Happy Qliking!

15 comments

QViewer: A Powerful, Light and Fast QVD Viewer for QlikView Ninjas

Featured Image: QViewer: A Powerful, Light and Fast QVD Viewer for QlikView Ninjas

 

I am a QlikView Ninja!

QlikView Ninjas can quickly slice and dice QVDs with exacting precision. How do I do this with the skill of a Ninja?

Well, I use my Ninja Sword of course! In this case, my blade of choice is a finely crafted weapon forged from the hands of the developers at EasyQlik. This weapon is called QViewer. In today’s post, we will go through some great use-cases for QViewer.

 

What is QViewer?

QViewer is developed by Dmitry Gudkov, a friend of LivingQlik, a blogger in his own right over at BI Review, and a developer of tools like QViewer and EasyMorph. Dmitry has been in BI for over a dozen years. He knows what he is doing. Dmitry was also featured in our Expert Tips roundup.

 

QViewer is a utility that allows you to rapidly examine QVDs. With QViewer, you can quickly open your QVDs outside of any Qlik product, access valuable lineage and metadata, filter (select) and search row-level content, and with the help of the free version of EasyMorph, even transform the contents of your QVDs.

We all know that QVDs are the building blocks, the foundation of our QlikView deployments. The ability to transparently inspect QVDs outside of QlikView saves a load of time, and simply provides clarity on the fields and values and the changes that might need to be made to them.

I thought it would be a good idea to show you how I utilize the tool with some specific use cases.

Data Lineage – What App created this QVD?

I typically get into situations where there tons of QVDs laying around all with different levels of transformation and business purpose. Sometimes it is difficult to know which QlikView application created a specific QVD.

Getting this information is easy with QViewer. Double click on the QVD to open and then select Metadata –> File Metadata (F4) to find out what app created the QVD. You can see the path of the qvw and then also the key data sources and SQL statements if applicable that were involved in the creation.

This window also instantly gives you the file size, field count and row count.

Metadata – Optimization Opportunities

Call me crazy, but finding optimization opportunities is one of my favorite Qlik past times. QViewer gives you some good techniques to find these opportunities. Double click a QVD to open in QViewer and then click Metadata –> Table Metadata (F5).

This view provides table level meta data. Specifically, the number of unique values, the uniqueness percentage, density, size in bytes and average bytes. Sort this by the size in bytes to see the fattest fields and then focus on the fields with a high uniqueness percentage.

Then choose a field and select Values (F2) to see all the values. The url field below was taking up 13mb of data in the application, but upon examination, it seems that the majority of the url is a fixed path with only the final page reference being unique. So I used the Generate Load Statement and used QlikView to subfield the url into 2 fields, one with the fixed url and one with the number. These two new fields can be easily put together once in the front end. In the meantime, I reduced that field from 13mb to 3mb. That is a huge savings.

The Table metadata view will also show you QVD field comments. You can actually add field comments metadata to your QVDs before you store them using the Comment command:

Then you can see the comments in QViewer:

Validation and Inspection

I find that when creating my ETL layer QVDs, it is time consuming to confirm (or validate) the transformations as I code them. It seems that only a small part of my time is spent putting in the code that transforms my data and I spend the majority of my time figuring out how to spit out the QVD with the change and get it loaded onto my screen to validate the change worked.

QViewer DEFINITELY is helpful in this regard.

Once you have loaded your QVD, I no longer need to create a separate throw-away QVW to load my QVD or otherwise adjust my script to retain data that I would normally drop. I just open it in QViewer. It loads incredibly fast.

In the example below, the users wanted a Customer Group field to be manually populated based on the Customer Number. If the customer number was numeric, we wanted the Group to be “Adhesives”. If the customer number was alpha, we would use “Coatings”.

I used the isnum function to separate the Customer Groups

Then instead of using QlikView to load the QVD and check my work, I just opened the QVD again in QViewer.

Firstly, it loaded over one million records in .17 seconds. The response times of QViewer are amazing.

Secondly, I was able to actually make selections on the Customer Num field to check my results. Simply right click on the field and select Fields. A listbox pops up, allowing you to make selections just like you would in QlikView.

This is one of the most impressive features of QViewer because not only can you make selections like you can in a QlikView dashboard, but you can search, filter on number or text, view the metadata for the field and also see the frequency of each value. You can actually make multiple selections to inspect the QVD like you would any QlikView application.

I selected a few items that were numbers and a few that were alpha. It looks like my transformation was correctly applied.

Transformations in QViewer with Help of EasyMorph

One of the advertised use-cases for QViewer is to link it with the free version of EasyMorph. <add link>. EasyMorph is a great stand-alone ETL tool that enables you to make transformations in your data sources including MS SQL Server, SQLite and generic ODBC. One of the data sources that EasyMorph can transform and write back to is QVD, of course!

I would imagine EasyMorph is a more useful feature for Tableau since that tool does not have an ETL layer or really any scripting ability at all. And indeed Dmitry confirmed to me that this was the main reason he developed the tool. As we all know, QlikView and Qlik Sense have ETL covered for the most part.

So just to conduct a test, I went ahead and did the same transformation with EasyMorph using my Customer Group example from above.

I opened my source QVD in QViewer and then clicked MorphIt.

I won’t go too deep into the use of EasyMorph, but I was able to pretty easily create the same transformation and create a step to Export the changed table back to Qlik QVD format.

To get back to QViewer, you have to reload the QVD.

Inspecting a Resident QlikView Table

Now this is a neat use case. There are many times when I would like to see the contents of a resident table in QlikView. I generally wish to see the table at a specific point in the script as I am coding a transformation in the resident table.

Usually in QlikView, I have to insert an exit script and then possibly drop some of the other tables that still exist to avoid creating complex synthetic keys. This is all time consuming and error prone because when my test is complete, I usually forget to undue the various code pieces I used that allowed me to test the table.

Using a subroutine <add link>, I can call the script to export any resident table into a QVD as the script runs. This allows the script to continue and also neatly deposits QVD files maybe even multiple files to a directory where I can then view them in QViewer.

Here is the subroutine. It should be placed towards the beginning of your QlikView script.

Then you simply call the sub wherever you want to create a QVD file.

When you execute the script, the QlikView script will run uninterrupted, and QViewer will open up in another window with the resident table you requested. Behind the scenes, we have actually told QlikView to create a QVD and then asked QViewer to open it.

QViewer is Super Fast. It will save you TIME

QViewer appeared to be incredibly quick at opening the QVDs on my drive. I decided to conduct a small experiment to test if QViewer is faster at opening a QVD than QlikView would be initiating a 100 row preview from the script.

For me, this time savings paid for QViewer in the first day of use.

To set up the experiment, I created a 100 million row QVD with random data (using the Ctrl+Q+Q trick). Please be careful when doing this because scalability tests can greatly impact the resources of your machine. You might want to keep your task manager open so you can watch the RAM being used while the QVD is being built.

I first opened the QVD from my file system with QlikView. I don’t have a system verified time for how long it took to get the preview, but I used a stopwatch and it took about 3 seconds to open the preview. It was not actually usable at that point because the splash screen stays on your screen for 10 seconds (it seems to me like this time has actually been lengthened lately). I will digress for a second.

Expert Tip: You CAN get rid of the QlikView splash screen forever! Go to Help –> About QlikView and then right click on the QlikView logo in the bottom left of the popup. You will find a series of advanced settings you can manipulate. Scroll down to SuppressSplash and set the Value to 1.

A more test more suitable to being able to explore all the data in the QVD would be to time how long it takes to create a script to load the QVD into QlikView, load it and then create a table of the QVD. This takes some manual clicking, but working on a plane with my fat fingers, this took about 3 minutes. The biggest issue with using QlikView for this is that the table in the dashboard takes time to render for 100 million rows and consumes quite a bit of RAM.

Then I tried the same experiment with QViewer. It indexed the file in exactly .60 seconds.

I was able to freely scroll through the data in the main window and open the list box pane on a highly unique field and apply a selection with no discernible delay.

Dmitry cautions me that the measurements coming out of QViewer are only reporting the indexing of the QVD. And also the newest version of QViewer will be a tad slower as the low-level processes used to load the binary data are changing to better handle larger QVDs. But in real terms, I was gaining value from the QVD within seconds rather than minutes from QlikVew.

This experiment made clear to me the real advantage of QViewer. Much of my frustration with QlikView in general is the “waiting around” whether that is for reload scripts to execute, objects to render or splash screens to disappear. Using QViewer allows me to reduce some of that pain specifically for loading QVDs and displaying their contents for validation purposes. For me, this time savings paid for QViewer in the first day of use.

Where to Get QViewer

You can start with the free version of QViewer. The limitation is that it will only load 100,000 records. As you and I know, even though that sounds like a lot of records, it really is not in the land of QVDs.

Go to EasyQlik now and pick up this great tool.

 

Happy Qliking!

0 comments
Page 2 of 19
1 2 3 4 19

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

other sites

ASKQV One Spot – Many QV Blogs
Visual Data Group My QlikView partner
Official Qlik Community The great Qlik Community
QViewer Handy QVD Viewer

Show Buttons
Hide Buttons