Have you got a function similar to SQL NVL ?

I work with a group of people who are all experts in SQL. They all also have varying levels of technical skills and QlikView skills. One question I get frequently is whether QlikView has a function like the SQL NVL function.

For those of you not familiar with relational database SQL language, the NVL function takes two arguments: a field name or expression and a default value to be returned if the first argument is null. For example, a SQL database query may include the function like this:

  NVL(ORD_DISCOUNT, 0) AS ORD_DISCOUNT

And that would tell the SQL processor to look at the value of ORD_DISCOUNT and if it is null then return 0 as the function value otherwise return ORD_DISCOUNT.

When people ask about achieving the same thing with QlikView, I usually start by telling them that they can code an “if” statement like this:

  If(IsNull(ORD_DISCOUNT), 0, ORD_DISCOUNT)

and then I explain that there is a built-in QlikView function that can be used similar to NVL as long as the field you are checking is supposed to be numeric. It is the Alt function.

The Alt function accepts any number of arguments and looks at each of them going from left to right and returns whichever one is a valid number. So, repeating our example, if ORD_DISCOUNT is null then the following function will return a zero but if ORD_DISCOUNT is a valid number then the function returns ORD_DISCOUNT:

  Alt(ORD_DISCOUNT, 0)   

The Alt function will treat the last or rightmost argument as an “else” condition and return that value if none of the preceding arguments are numeric. The rightmost value need not be numeric so you could code something like this:

  Alt(ORD_DISCOUNT, 'Discount is missing')

The Alt function may be used in the loadscript code or in chart expressions.

If you are interested, the QlikView Help (search in Help for Conditional Functions) shows an interesting example for how the Alt function can be used to identify a date when the date value may be any one of several different date formats.  

  ★★★

Is My KPI Getting Worse or Getting Better

We have a lot of QlikView documents that measure various kinds of business planning performance. The measures are usually a calculation of the difference between planned values and the actual values. Then the calculation is normalized by dividing it either by the planned or actual values so that the measure can be shown on a document as a percentage.

Key measures or key performance indicators (KPI) like that, if they are well chosen and well designed, give people an idea of how well a business process is performing. I believe that the measure can be even more useful if we add to it an indication of whether the measure has been getting “better” or getting “worse” over time.

Most of the performance measures we use are aggregation calculations that can be used with various dimensions like customer, product, or geographic region. When you show a measure like that in a line graph where the x-axis is time (for example, weeks or months) the line is usually jagged with up and down measures and maybe a “hump” or two. 

The first thing you can do to help someone figure out if the measure is getting better or worse is to use a trendline. In a line graph, go to the chart properties and pick the expression where you’d like to see the trend, find the trendlines section of the expression dialog and click the Linear check box. Now, click OK and you should see that QlikView has drawn a straight line across your jagged line graph. The upward or downward slope of the line can give a person some idea of whether the measure has been getting better or worse.
Note: I usually modify the Presentation tab of the chart properties and set Trendline Width to a narrow line (maybe 0.5) so it doesn’t compete with the main graph line for attention. 

If you stop reading this post at this point, then you’ve already picked up something useful.

Some users of these documents say that they would like the document to point out the areas where a particular measure is getting very bad so they know where to spend their time or resources. We can use a function already built in to QlikView to provide us with a number that would, if it was a trendline in a graph, be the slope of the line. 
If the line is going uphill from left to right, then that would be a positive slope and if it is going downhill then that would be a negative slope. A flat, level line would have a slope of zero. I’m going to describe here how to build a calculated dimension for a chart that can show the 20% of things where a measure has been deteriorating the worst over time.

First, to simplify the description, imagine that you have taken the performance measure expression from your straight table and put it into a variable. We’ll call the variable vKPI.
The time dimension field in the example is PWEEK.
And we are going to use this calculated dimension in a straight table where it will be labeled 20% of Projects Where KPI Has Deteriorated the Most
The linest function is used to calculate the slope of the imaginary graph line. 
The Rank function will help identify the 20% of worst performing PROJECTs. 
Aggr is used to create an array of PROJECT values for our chart dimension. In this example, the higher values indicate a deteriorating measure but you can flip the calculation around if your logic is reversed. 

=aggr(if((Rank(
  linest_m( 
  aggr(
  =$(vKPI)
  ,[PROJECT],PWEEK)
,PWEEK)
,1)-1) / Count(distinct total [PROJECT]) < 0.20, [PROJECT]), [PROJECT])

I know this is one of the more complex topics I’ve tackled in this blog but the result in a document that analyzes key performance indicators is valuable. In the documents where I’ve used this concept I usually provide a method for the user to choose what dimension field they would like to use, so it is not limited to PROJECT so the user could choose PRODUCT, SALESPERSON, or REGION, etc. And then for even more flexibility, I put the calculated dimension into a cycle group made up of other calculated dimensions that identify things that need attention.

In my next post, I’ll show how a related calculation could be used to color code items in a chart so that the items where performance has been getting worse are tagged with red and items where performance has been getting better are tagged with green.

  ★★★

Chart Adjusts to Whatever Fieldnames are Loaded

I’m often called on to validate a new data file from a client or to examine a new data extract. My first step is to load data into a copy of Steve Dark’s Data Profiler (search on quickintelligence data profiler). I’ve made a few modifications to Steve’s original document – here are a couple of the most useful.

I added a line to the loadscript that uses preceding load to add a record number to the rows being loaded. My line is added between the table label and the first line of the regular Load statement. It looks like this:

DATAFILE:  //table label
Load *, recno() as zrecno;  //this is the preceding load line
Load   //this is the beginning of your "regular" Load statement

For the second set of modifications, I added a new sheet or tab which I named Table.
On the new tab, I added a multibox and then I added, not fieldnames, but 20 expressions. You can add any number you like but 20 seemed like plenty. These are mostly copy and paste so they are not a lot of typing and you can start by copying the expression shown in this blog and pasting it into your document. The first multibox expression looks like this:

[$(=only({1<$FieldNo={1}>} $Field))]

That expression evaluates to the fieldname corresponding to field #1. Then I add a second expression to the multibox:

[$(=only({1<$FieldNo={2}>} $Field))]

You can see that in the second expression, I changed $FieldNo={1} to $FieldNo={2} so that the second expression evaluates to the fieldname corresponding to field #2. I did that for each of the 20 expressions so that the 20th expression in the multibox contains $FieldNo={20}

Then, I added a straight table chart. I used zrecno as the dimension. Then, I added 20 expressions. For the first expression, I typed this:

[$(=only({1<$FieldNo={1}>} $Field))]

And for the label, I typed this:

$(=only({1<$FieldNo={1}>} [$Field]))

Again, the rest of the expressions are similar, each time changing the $FieldNo={1} to be 2 for the second expression and its label, $FieldNo={3} for the third expression and its label and so on for each of the 20 expressions. Click OK when you’re done.

Now, you have a multibox and a straight table chart that appear as though you typed in the 20 fieldnames. But, these objects will adjust themselves to whatever fieldnames are loaded. If you load a file with less than 20 fieldnames, the “extra” ones will just be null.

You can copy and paste these objects into other QlikView documents and they will adjust themselves to whatever the first 20 fieldnames are in the document (see important info in first blog comment below). But, I think these are most useful in a document where different fieldnames can be loaded each time.

I checked Dropdown Select for all columns in the Presentation tab to make it easier for the document user to make selections on the chart. 

In order that we don’t burn up all of the memory on the computer, I added a calculation condition to the straight table chart with this expression =if(count(zrecno) < 200000,1,0) so that the chart will refuse to appear if there are more than 200,000 rows. I customized the error message for the calculation condition to say, “There’s more than 200k rows. You have to make some selections to reduce the amount of data in this chart.” A calculation condition like this is a good idea whenever you have an object that might try to show more data than is possible with the available memory.

  ★★★ 

Function With Flexible, Choosable Dimensions

I worked on a document last week that calculated a performance indicator metric based on budget amounts compared to actual spending amounts. The chart expression for the calculation used the Aggr function and two dimensions: spending program and geographic region. Simplifying from the actual expression, it looked something like this:

Sum( Aggr( Fabs( Sum(spendqty) – Sum(budgetqty) ), SProgram, SRegion))

The business requirement had changed and I needed to provide a method so the person using the document could choose the level at which to calculate the differences and the level could involve one or two different fields and a choice of weekly or monthly time period.

I decided to use a method that would eliminate the need for if statements in the expression and also provide an easy way to add more choices in the future. First, I built an inline load of the choices in the loadscript (a data island since the field names do not appear in any other table):

CALC_LEVEL:
Load * Inline
[CALCLVL_DESC : CALCLVL_CODE
Program, Region, Weekly : SProgram, SRegion, WEEK_start_date
Program National, Weekly : SProgram, WEEK_start_date
Region, Weekly : SRegion, WEEK_start_date
Program, Region, Monthly : SProgram, SRegion, Fiscal_period445
Program National, Monthly : SProgram, Fiscal_period445
Region, Monthly : SRegion, Fiscal_period445
] (delimiter is ':');

Note that I specified a colon as the field delimiter so that I could include commas in the field values of CALCLVL_DESC.

In the document, I created a Listbox for the CALCLVL_DESC field and titled it “Please choose a calculation level:” So, the business user can see the possible choices using familiar words that make sense in a business context. The listbox was configured for Always One Selected Value. When the user makes a selection of CALCLVL_DESC it means that there is only one available value of CALCLVL_CODE which is important for making the expression work correctly.

The dimension part of the Aggr function was changed so that the expression now looks like this:

Sum( Aggr( Fabs( Sum(spendqty) – Sum(budgetqty) ), $(=CALCLVL_CODE) ))

That makes the dimension part of the Aggr function take on the specific field names listed in the CALCLVL_CODE value. It allows the user’s choice to automatically control the fields used in the dimension. Adding new choices in the future would be a matter of adding new lines to the inline load.

This is a powerful technique, it is not limited to the Aggr function or an inline load data island. It can replace an entire expression or any part of an expression based on the current selection (as you read that sentence, keep in mind how flexible and powerful the concept of selection is for QlikView).   

★★★

Problem: Drag and Drop Straight Table Columns Not Working

One of the other consultants at work came to me last week with a seemingly odd problem. She had a document with a straight table that originally had one dimension column. She added two more dimension columns and QlikView added the new columns on the right-hand side of the table. But, my co-worker wanted all three dimension columns to be on the left of the table which is the way we usually set up a straight table. Normally, dragging the new columns over to the left should be quick and easy… but, no matter how many times she tried, the new columns could not be dragged and stayed stubbornly on the right.

After close examination, some trial and error, and a bit of logical thinking; the source of the problem became clear. The thing that was interfering with the drag-and-drop of the dimension columns was the few columns in the chart that were not visible. In this case, there were two kinds: a hidden column, and three conditional columns that would only be visible under specific conditions.

The source of the problem suggested a solution:  right-click on the straight table, select Properties and choose the Presentation tab. One by one, click on each of the columns shown on the Presentation tab and make sure the Show Column radio button is selected for all columns (not Hidden or Conditional). Then, when all columns are visible, the drag-and-drop will work normally. After rearranging the columns as desired, use the chart Properties and Presentation tab again to restore the hidden columns and conditional columns.
One of the blog comments below suggests there is another type of non-visible column that would affect a Table Box: a column referring to a field no longer available in the document. You would have to edit properties for the Table Box and remove the field that no longer exists to restore drag-and-drop capability.

★★★

Note About Dollar-sign Expansion

Katrina, from the Marketing Group, came to me yesterday with a QlikView document she was working on. It had three scatter plot charts arranged side-by-side and she wanted to make the side-by-side comparison easier by making them all have a similar Y-axis. She knew she wanted something for the static max property of the charts so that each would have the same maximum value for the Y-axis. She knew the basic expression to calculate the maximum value but the charts have a cyclic or cycle-group in the dimension and she needed an expression that would work with the cycle group.

I knew that she needed a dollar sign expansion (to handle the cycle group) and an aggr function (to find the maximum value by dimension) so I scribbled an expression like this on a sheet of note paper and Katrina left to try it out:

Max( aggr( your-calculation-of-y-value, $(=getcurrentfield( your-cycle-group-name )))

An hour later, I got an email from Katrina saying that the expression wasn't working. I decided to take a closer look at her document. The cycle group dimension in the charts is named “Scatter ExErr”. It contains a space or blank in the middle of it—so when it is used in an expression it must be surrounded by square brackets like this [Scatter ExErr]    (Note that double-quotes should work as well as square brackets.)

But, the expression still wasn't working correctly and it returned a null value. When I examined the field names used in the cycle group I saw that some of those also contained a space inside the field name. When you use a dollar sign expansion with a getcurrentfield function in your expression you can think of it as though the dollar-sign expansion becomes the field name before QlikView calculates the expression. Since the field name contains an embedded space then it must also be surrounded by square brackets and in this expression that means surrounding the dollar-sign expansion in square brackets. Katrina’s expression now has two pairs of square brackets and looks something like this:

Max( aggr( your-calculation-of-y-value, [$(=getcurrentfield([Scatter ExErr] )] ))

In documents that I develop, I avoid putting embedded spaces into any names that are not actually visible in the charts and I use an underscore character if it is needed to make a field or object name easier to read. If you decide to name things with names containing embedded spaces remember that you usually have to surround the name with square brackets or double quotes when the name is used in a function or expression.

★★★

Maven's Sliding Window

Expanding on the example from last time about loading unstructured text data (to read about it, search on QlikView Maven Loading an Unstructured Text File); here’s one way to examine the data. I call it Maven’s Sliding Window. My requirement was to design a method that would allow a straight table to review the rows of text even though there may be a million rows or more. It had to work even though there may be a special selection applied (for example, a selection of all rows containing the word “program”). The straight table had to show just a very small subset of the rows at any one time to avoid the memory issue that would occur if the straight table tried to show many thousands of rows.

[If there was no selection already applied then this task is easy and no special object is required—just select a contiguous group of 20 rows and you can page though the data 20 rows at a time with the Page Up or Page Down keys on your keyboard.]

First, create a variable named vFinger with a current value of 1 and a constraint so that it contains positive integers. Now, create a Slider Object to update the value of vFinger. In the General tab set Mode to Single Value, set Min Value to =min(recno), set Max Value to =max(recno), click the static step box and put 1 in the static step box. I made the Caption tab title say “Window Pointer”. Make the slider have a vertical orientation to help make the usage a little more intuitive.

Now, set up a straight table object. Set up a calculated dimension named recno with an expression like this:
 =If(aggr(rank(-if(recno>$(vFinger),recno)),recno)<=20,recno) 

And set up one expression defined simply as  logtext  (remember that we are still working with the data loaded in the example from 6May2013)
On the Sort tab specify that the dimension recno is sorted ascending numeric.
In my simple example, I made the object title a text expression like this:
  ='Sliding Window Text from $(File_Pathname):'
so that the title uses the variable containing the file pathname to show where the data came from.

That gives us a straight table that is showing only the records surrounding the record number specified by the vFinger variable. Using the slider that controls vFinger controls which section of text appears in the straight table. You could make the “window” bigger by changing the numbers in the calculated dimension.

Add a Page Down button that updates the value of vFinger to move the window “down”. The metaphor of a sliding window means that sliding down goes toward larger recno values and sliding the window “up” goes toward the lower recno values. The button action would be Set Variable to update the vFinger variable. The action Value would be  =max(If(aggr(rank(-if(recno>$(vFinger),recno)),recno)<=20,recno)) 
That adds to the vFinger variable while staying within the recno values that are selected.
A similar Page Up button would contain a Set Variable action to update vFinger with a value like this  =min(If(aggr(rank(if(recno<$(vFinger),recno)),recno)<=20,recno)) 

If I’m working with long text records then I often set up slider objects to control which text columns appear in the straight table. For example, you could set up a slider with horizontal orientation to control the value of a variable named startcol and a slider to control a variable named endcol. Then, in the expression, instead of simply specifying logtext, code this text expression:  Mid(logtext,startcol,(endcol-startcol)+1)
That will allow you to control which text columns appear in the straight table and you can slide that part of the “window” left and right.

Note that you can save the document and later when you re-open it, the sliding window will still show the same group of records since it is driven by variable values that are saved along with the document.

My example here had a record number field to use as a dimension for the straight table. But, any data that forms a series would work just as well; for example, dates, time of day or timestamps, degree/minutes/seconds of longitude, hours of operation of a machine, measurements from the Hadron Collider, etc. 
The next blog post will discuss using the power of QlikView selection and set analysis to help us examine the text data.


★★★

Expression Overview - Find/Replace

The desktop QlikView Expression Overview is a useful tool for searching expressions in a document or doing a Find/Replace on terms and values in the expressions (from the desktop menu Settings->Expression Overview). Many people put expressions or parts of expressions into document variables mainly so that they have only one thing to change when the expression needs to change instead of changing individual expressions in multiple places in a document. With the Find/Replace functionality you can accomplish the same thing: make the change in only one place and effect the changes wherever needed throughout the document.

If you use text expressions for column labels and object titles then the Find/Replace function can change those too. Find/Replace will also change text in the expression comments; something I find useful since comments often refer to field names and function names. Some people put a little label or tag in a comment line in the expression to help them find those specific expressions later. For example, you might put a comment in the expression that looks like this, /* Tushar, please review this */ to help a quality review person find the expressions that need to be reviewed. (To make a comment in the expression either put /* before the comment and */ after the comment, or, use // to indicate that the remainder of the line is a comment.)

One thing to remember is that the Find or Find/Replace will not find hidden objects. When I am using Find/Replace functionality on a document with hidden objects I usually change the document properties Security tab and click on Show All Sheets and Objects. Then, Find/Replace can make a change to all expressions, even those on the hidden objects. After making the change, update the Security tab again to uncheck the Show All Sheets and Objects box.

If you want Find/Replace to avoid changing expressions in a particular object then you can temporarily hide the object while making the Find/Replace changes. To temporarily hide an object go the object properties and type 0 into the Show Condition (type a zero character). After the Find/Replace changes are finished, go to Document Properties, Security tab and click the Show All Sheets and Objects check box. Then, find your object and clear the Show Condition box in object properties. Go back to the Security tab and uncheck Show All Sheets and Objects and your temporarily hidden objects are visible again.

* * *

Count of Rows in a Chart

Once in a while someone asks me if there's a way to count the rows in a chart and put the number of rows in the chart title. I usually respond by asking, "why would anyone want to do that?" Usually, an expression that counts the distinct values of the fields used as the chart dimension is close enough. If you want a method that actually counts the rows in a straight table chart, here's something I have used that counts the rows and puts the count into a variable named chart_row_count. It will need to be modified for your environment and tested. (***make sure you also see the suggestion near the end)

Create a macro in the module code that looks like this:

Sub Count_Rows

'-------------

'/* Put your chart object id in the next line */

Set CHRT=ActiveDocument.GetSheetObject("CH70")

On error resume next

'/* in the next line, put an upper limit on the */

'/* loop bigger than your expected chart row count */

For i=1 to 500

ccontent=CHRT.GetCell(i,0).text

If Err.Number <> 0 then

Err.Clear

On error goto 0

ActiveDocument.Variables("chart_row_count").SetContent i-1, True

Exit For

End If

Next

On error goto 0

Set CHRT=nothing

End sub

The count in the variable includes the total line if there is one. You can use the variable value in a text expression in your chart title. One thing you must do that will vary from document to document depending on your requirements-- you must figure out how to trigger the macro when the number of rows in the chart might have changed. Triggering when a selection changes is easy to do. Triggering when a cycle group is changed will be harder. For testing, use a button to trigger the macro.

= = = = = = = = = = = = = = = = = = = = = = = = = = = = =

*** nrbhattad suggests this much simpler macro for finding row count:
set chart=ActiveDocument.GetSheetObject("CH01")

ActiveDocument.Variables("chart_row_count").SetContent chart.GetNoOfRows, true

.

The Case of the Disappearing Object

A few weeks ago my co-worker Kim called me about a problem she was having copying a chart from one report to another. Copying any QlikView sheet object from tab to tab or report to report is very easy but she was having a problem pasting the object into a new report. There was no error message but it was obvious that the chart was not being pasted successfully.
We discussed the possibilities and I focused on security setting issues but, by the end of the call, nothing had worked.

Two hours later, Kim called me back – she had figured it out and, like many good problem solutions, it seemed so obvious in hindsight. The problem was that the object had a Show Condition defined that would make the object visible only under a specific data condition. The data in the new report was different and the condition was not satisfied. So, the object was behaving as the Show Condition directed and it was simply not visible in the new report. Now, I know to be conscious of the Show Condition when copying an object.

★ ★ ★

Benefits of Concatenating a Code and Description

In the typical business report in a corporate environment you need to include quite a few selections either through a list box or multi-box. There may be selections for product, customer, sales executive, division, channel, product type, country, fiscal period, etc. You probably have codes for those things in your corporate databases and, if you’re lucky, you also have descriptions that go along with the codes.

When we build a new QlikView report we design the loadscript to load not just the codes but also the concatenation of the code and the corresponding description separated by a hyphen. That part of the loadscript code might look like this:
PRODUCT_MASTER:
SQL SELECT
  PRODUCT_CODE,
  PRODUCT_CODE||' - '||DESCRIPTION AS PRODUCT
FROM PRODUCT_MASTER;

Then, we use the PRODUCT field in list boxes, multi-boxes and in the chart dimensions. The reason this idea is so valuable is because of QlikView’s behavior when you are making a selection: if you click on the title bar of a list box or on the down arrow of a multi-box and then start typing, the visible list of items changes to show only the ones that match what you have typed so far.

For example, if you click the down-arrow on the multi-box selection for our example field, PRODUCT; and then start typing baby -- all of the products that contain the word “baby” in the product description move to the top of the list high-lighted with a white background. Keep typing, baby lotion… and now only the products whose names contains the words “baby lotion” are on the top of the list. At that point you can either press the Enter key which will select all of the high-lighted products or you can click on an individual product with your mouse. If that field only contained product codes then you would be limited to typing in a subset of the product code – which you can still do, of course, with the concatenated field values but the extra value of being able to type in a partial description to help with the selection is extremely useful.

A description for a code value is so useful that we have sometimes loaded the description data from a spreadsheet when it isn’t available on the corporate database. Then it is joined and concatenated to the code value in the loadscript.

Using the concatenated code and description as a dimension in the charts also works well. The field still sorts by product code since that is the leftmost part of the field value. Often we shorten the field width so that just the product code is showing on the screen but moving your cursor over the field reveals the complete description.

Copying a Chart From One Report to Another

Sometimes you want to copy a sheet object from one QlikView report to another. You might have two reports; say one for the finance department and a separate report for the supply chain department that both load similar data from the database. If the supply chain analyst develops a really great chart for the supply chain report and the people in finance could use it then you could copy the chart from the supply chain report to the finance report. You could always just recreate the chart from scratch on the new report of course, but it is easy to copy the chart from one report to another using Windows clipboard. Here’s how it could be done using our example:

• Open both reports


• Make a back-up copy of the "finance report" (the one you are going to change) by doing a save-as under a different name


• Now, go to the supply chain report that has the cool chart. You could use the Window menu to select the other report. Click on the chart so that the chart is selected (when the chart is selected the title bar at the top of the chart changes color).


• Select Edit->Copy from the menu


• Switch over to the report you are going to change, the "finance report" in this example. Make sure you are looking at the finance report and select the tab where you want the chart to appear


• Select Edit->Paste from the menu and the cool chart will get pasted into the finance report. It will appear in approximately the same position on the sheet that it occupied on the original report.


• Finish up by moving the chart to the position you want, make any required changes to chart properties (like data field names or title, etc.) and save the changed report

You can use this method to past a sheet object into several different reports or paste the same sheet object into multiple tabs on the same report. I use this method all the time when developing a new report with multiple tabs.

Another method that can be used to copy a chart within a single report is to click on the chart, select Properties, and then select Clone. A copy of the chart will appear just slightly offset from the original one. You can use your mouse to drag the cloned chart either to a new position on the same tab or drag it to one of the other tabs at the top of the report and, when you see the curved arrow appear, release the mouse button and the chart will now be on the other tab.