QlikView Maven

Count of Rows in a Chart

2011-12-16T17:40:00.006-05:00

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

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

Select Top 4 Values On Open

2011-11-18T13:01:00.005-05:00

I was working on a document that contained a lot of data and the performance when the document was opened or when the user moved from tab to tab was a little slow. On my own laptop when I save documents like that I usually make a selection of a small amount of data so that later when I open the document again it will open quickly. I wanted to do something like that for the users -- some kind of selection that would happen automatically when the document is opened, but it had to be a selection of data that would be useful and easy for the users to understand.

The data in the document was organized by a date field named POSTDATE so I decided that I would like the document to automatically select the most recent four POSTDATE values when it was opened. Most users look in the document for the most recent data and anyone who wanted to review older data could easily add older dates to the selection through the multibox.

The method I used is to create a trigger that would fire when the document is opened. The trigger would do a Pareto Select. Now, I'm not saying that this is the best way to accomplish the selection and it certainly is not the only way, but it was easy and didn't involve writing macro code.

Pareto Select is used to select a percentage of values in a field based on ranking of a value or expression. It is commonly used select things like the top 20% best selling products. In this case I wanted to use it to select not a fixed percentage but the four most recent POSTDATE date values. This is what I did:

From the menu choose Settings->Document Properties and choose the Triggers tab.
In the document event triggers click the Add Action button for the OnOpen event.
Click Add Action and in the Add Action dialog choose Action Type=Selection and Action=Pareto Select then click OK.
In the Actions dialog type POSTDATE into both the Field and Selection boxes. POSTDATE is not only the field I want to select but also the expression value I want ranked.
Then, in the Percentage box enter =(4/COUNT(DISTINCT POSTDATE))*100
And click OK. Save the document and the next time it is opened it will automatically select the four most recent POSTDATE values.

A similar method could be used to do automatic selection of any number of top values of any field. Pareto Select does its selection based on the current Possible values. If you wanted to ignore the current selection and select a percentage of all values then you should add an extra action to the trigger to clear the field before making the Pareto Selection.

Testing trigger actions is most easily done by defining the same actions for a button and then you can test the action by clicking the button. When you're done testing, remove the button and make sure the actions are associated with the proper event.

= = = = = = = = = = = = = = = = = = = = = = = = = =
Author's Note 6Dec2011:
This method didn't work the way I wanted when the document already included selections on the POSTDATE field. I couldn't find any good way to deal with that using the available trigger actions. I eventually replaced the Pareto Select action with a macro action that selects the four most recent POSTDATE values. The macro code looks like this:
sub Top_4_POSTDATE
set fd=ActiveDocument.GetField("POSTDATE")
fd.Clear
fd.TopSelect "=if(aggr({1} rank(POSTDATE),POSTDATE)<=4,POSTDATE)",4
set fd=nothing
end sub

* * *

Replace IF in Expression with Set Analysis

2011-11-12T16:07:00.007-05:00

We often come across chart expressions that look like this:

=sum(if(SLSTYPE='DOMESTIC',SLSTOT))

It is easy to understand and if your document is a normal size and working well then leave the expression alone. But, if the document is large and you are looking for ways to speed up chart performance then consider changing the expression by replacing the "if" in the expression with the much faster set analysis syntax. Remember that set analysis is related to the concept of QlikView selections. Set analysis syntax works by specifying temporary selections within the function. So, if we wanted to consider set analysis for our example expression we should imagine whether the expression would work ok if we removed the "if" from the expression and, instead, made a selection on the SLSTYPE field to select the value DOMESTIC. If the expression would work ok in that situation then we can change the expression to use set analysis like this:

=sum({$<SLSTYPE ={'DOMESTIC'}>} SLSTOT)

We couldn't do that if the SLSTYPE field wasn't associated with SLSTOT because selecting the value DOMESTIC would have no effect on the expression. For example, if SLSTYPE had been defined in the loadscript as a Data Island unrelated to any other data then the expression wouldn't work correctly not matter how you coded the set analysis syntax within the sum function.

Another important consideration is whether or not the report is designed for users to make their own selections on the SLSTYPE field. If users are making SLSTYPE selections then they may not like an expression that overrides their selection within the expression. If the document allows user selection on the SLSTYPE field then we should define a separate field in the loadscript that will contain the exact same values as SLSTYPE and be part of the same QlikView table and same rows. Maybe call it SLSTYPE_SA and then we can code the set analysis part of our expression using the SLSTYPE_SA field and the expression will work correctly while still respecting all other user selections.

* * *

Increasing Likelyhood of Duplicate Values While Loading Data

2011-11-04T16:04:00.006-04:00

I was working on a QlikView document last week that loaded several kinds of sales forecast data and also actual sales for a chemical manufacturer. The forecast data came from a statistical forecasting application. The charts on the document were simple and showed the forecast data rounded to integer numbers of tons.

The document contained a lot of data and the performance was a little slow when making selections so I looked for ways to improve performance. Examining the forecast data, I saw that although the quantities in the database were expressed in tons almost every quantity included a decimal fraction. Now, QlikView is very good at compressing data and especially good at compressing data with duplicate values since it stores each unique value for a field only once. This forecast data did not contain a lot of duplicates because of the decimal fractions. I saw that if I rounded the values as they were loaded then values like 1.001, 1.00032, 1.2, etc. would all be loaded as 1. After changing the loadscript to round values to integers as the data was loaded the report file size on disk was reduced by 40% and had a corresponding reduction in memory required. The charts on the report were not affected all.

This technique would not be useful in all situations. It would cause a lot of trouble if you rounded data values on some accounting documents! But, sometimes, altering data slightly as it is loaded to increase the likelyhood of duplicate values can save memory for a QlikView document. Besides rounding (not necessarily to integers, you can round to a specific number of decimal places), you can also increase the likelyhood of duplicate values by:

Truncating text data - keeping only the first ten characters, for example.

Trimming text data to remove any leading or trailing blanks.

Converting to all capital letters.

Converting non-abbreviated words to standard abbreviations, like state abbreviations in address data.

Removing the time stamp portion from date data

Converting date data to beginning-of-week or beginning of month dates.

Converting date data to month abbreviations if that is the level of data used in the document charts.

* * *

Using Oracle Configured for Different Countries

2011-10-23T12:29:00.003-04:00

We load much of the data in our QlikView documents from Oracle databases. A common query technique uses the Oracle Next_Day function to aggregate data into weekly quantities. For example,

Select Next_Day(INC_DATE,2)-7, Count(distinct INCIDENT_NUM) From INC_LOG group by NextDay(INC_DATE,2)-7;

will gather a count of security incidents by week. The Next_Day function in the example should return a Monday date. But, sometimes, when the Oracle database is configured for a European business then the way Oracle numbers days of the week will be different from a database in the U.S. A European database may give Tuesday dates for that example query.

In order to make the SQL commands in a loadscript behave like they do in the U.S. I add a single SQL line to the loadscript following the ODBC Connect string that modifies the Oracle NLS setting temporarily while data is loaded into the document. The line I add after the ODBC connect string is:

SQL ALTER SESSION SET NLS_TERRITORY='America';

Does anyone have a different way of dealing with this issue?

* * *

Looking Inside a Bookmark Without Opening It

2011-08-19T13:28:00.003-04:00

This blog post comes from a question asked on the QlikCommunity forum. The question was how do you look at the selections inside a bookmark without actually selecting and activating the bookmark?

I had never thought about how you might do that. It is not difficult to list out the possible values for a single field based on a bookmark whether the bookmark has been opened or not. For example, for a bookmark named Feb_Sales and a field named ITEM you can put a text expression like this into a text object or chart title:

=concat({Feb_Sales} distinct ITEM, ', ')

That will give you a list of the ITEM values separated by commas. Note that it is a list of Possible values, it will only be the Selected values if the bookmark includes a selection on that particular field.

This technique can be used to give the user a peek into the selected or possible field values in the bookmarks; perhaps helping a user decide which bookmark should be used.

* * *

A Chart Expression That Works Differently for Totals Row

2011-07-29T11:59:00.006-04:00

I built a QlikView document last week with a straight table chart where I needed to have some of the expressions work differently on the totals row than on the regular rows. One of the dimensions on the chart was date -- but the totals row needed to aggregate data for the most recent date only. I wrote an expression that takes advantage of the fact that the RowNo function returns null for the totals row. So, leaving out the actual calculation part, the expression looked something like this:
if(IsNull(RowNo()),
/* do the totals row calculation */ ,
/* else, do the regular calculation */)

A similar idea can be used if you want to change the color or bold the totals row. For example, you could use an expression like this for background color:
if(IsNull(RowNo()),yellow())

and make the totals row look like it is emphasized with a highlighter color.

* * *

Duplicate Rows in a QlikView Table

2011-07-15T14:11:00.002-04:00

You may someday have a situation where you suspect that you have some duplicate rows in one of the tables within your QlikView document. It can be difficult to verify if this is true with a chart or tablebox -- you will only see a single row in the chart or tablebox even though there are duplicate rows in the table. The duplicates, though, often cause trouble with expressions. You may see an expression value that is two or three times larger than expected; this is often a symptom of duplicate rows in one of the tables.

To help identify the problem, add a row number field to each row in the QlikView table. That will give you at least one field that is unique on each of the duplicate rows. You can do this without reloading the entire document. Here's an example-- First, save a backup copy of the report that you are working on. Assume that the table to which you want to add a row number is named REDFLIGHTS. Add these rows to the top of your loadscript:

REDFLIGHTS_X:
Add Load RowNo() as REDFLIGHTS_RowNo, * Resident REDFLIGHTS;
Drop Table REDFLIGHTS;
Rename Table REDFLIGHTS_X to REDFLIGHTS;
Exit Script;

Then, from the menu, select File->Partial Reload This will run the five lines you added to your loadscript and it will build a new table inside the document that is identical to your original table but with a rownumber field, REDFLIGHTS_RowNo, added to the table. Now, in a tablebox that includes the rownumber field or in a chart using the key fields as dimensions and using a count(REDFLIGHTS_RowNo) expression you will be able to identify the duplicate rows in the table. At that point you will have to figure out how to eliminate or handle the duplicate rows when data is loaded. Remember to remove the five lines from the top of your loadscript before you next try to reload data.

* * *

Loading Multiple Files With Varying Fields

2011-07-03T10:44:00.010-04:00

If you ever have a need to load multiple files into a single QlikView table and the files have similar but not identical fields then you might try the solution below. I’ve used this method where the files are historical data created at varying times and although the files have most fields in common, some of the fields have extra fields. For example, in a set of sales data files, the files for exported products might have extra fields for export license data.

Here is an example of the code from the loadscript. It loads a set of QVD files. It loads the first file normally, then loads all subsequent files using the Concatenate keyword. This allows the data, including the extra fields, to be loaded into the same QlikView table, SALES_TABLE.

set filectr=0;
for each sfile in filelist (e:\sales_history\QVD_SALES_MONTH*.qvd)
  If filectr=0 then
   SALES_TABLE:
   Load * from $(sfile) (qvd);
  Else
   SALES_TABLE:
   Concatenate Load * from $(sfile) (qvd);
  EndIf
  Let filectr=$(filectr)+1;
next sfile

★ ★ ★

Export All Data From All QV Tables to Text Files

2011-06-27T21:27:00.009-04:00

Last week one of our clients asked me to provide a method that they could use to export all of the data from within a QlikView document. They had hundreds of archived documents and they wanted to be able to export the data and use it in another application.
“Sure, No problem. I’ll email you some instructions.” I sent them instructions for exporting all of the data from all QlikView tables within a document into tab-delimited text files. This is what I sent to them:

1. Create a new folder on your laptop or server. The computer where you do this must have QlikView installed.

2. Copy the QlikView .qvw document file into your folder. This will make sure you don't accidently update the original document.

3. Double-click on the .qvw file to start up QlikView.

4. From the menu, select File->Edit Script. This opens up the Loadscript Edit Window.

5. At the very top of the loadscript, before the first line, copy and paste in these lines:

/* Export all QlikView tables to tab-delimited files */
if IsPartialReload() then
for ix= 0 to 100
let tn=trim(tablename($(ix)));
if len('$(tn)')<1 then
exit script;
end if
Store "$(tn)" into "EXPORTED_QV_$(tn).TXT" (txt, delimiter is "\t");
next ix
end if
exit script;
//

6. Close the Loadscript Edit Window by clicking the OK button in the lower right corner of the window.

7. From the menu, select File->Partial Reload

8. You don't need to save the document, but if you do save it, do not copy it back to the original location.

If everything works correctly, each of the QlikView tables will be written out as tab-delimited text files into the folder. For example, if the QlikView document contains a QlikView table named HISTCUST then it would write out a text file named, EXPORTED_QV_HISTCUST.txt. The first record will contain the field names.

Note the syntax for specifying the delimiter character. If your Store command simply includes (txt) without specifying the delimiter you will get comma-delimited text files. Use the file specification, (qvd), to get QlikView QVD files.

I would've preferred a more elegant piece of code using a Do While loop but I had trouble getting the test to run correctly and I had limited time. The code above should work fine for documents with under 100 QlikView tables.

* * *

Variable That Acts Like a User-Defined Function

2011-04-03T09:51:00.007-04:00

QlikView document variables are really just text strings with a name. In various places in the document where expressions are used you can include a variable name and QlikView will replace the name with the text string that the variable contains. It's a simple idea that can be used in many ways.

One variation on the way to use a variable is to include an argument with the variable where the argument value is provided in the larger expression where the variable is used. The technique is useful for number, currency, or date formatting. Here is an example:

Imagine that you have a document that contains some customer data including nine-digit telephone numbers and you want to show the telephone numbers formatted in U.S. style like this (123) 456-7899.

Create a variable named telno and enter this as the variable value:

'('&left($1,3)&') '&mid($1,4,3)&'-'&right($1,4)

When we use this variable in an expression, the $1 will be replaced by the argument value. It is a text replacement operation but, because it takes place within a larger expression, the effect is like a user-defined function. In our example, the customer telephone field is named CUST_PHONE. So, we can create a chart expression like this:

$(telno(CUST_PHONE))

That will show the customer telephone number in the format we need. The real value of the technique is apparent when the expression is long or complex or uses advanced techniques like user-selected formatting.

For two arguments you can use $2 and it will be replaced by the second argument. The third argument will replace $3, and so on. For example, if you had a telephone extension number as a data field then it could be used in our example chart expression like this:

$(telno(CUST_PHONE, CUST_EXT))

and then the variable value could include $2 which would be replaced by CUST_EXT. It might look like this:

'('&Left($1,3)&') '&mid($1,4,3)&'-'&right($1,4)&' ext '&$2

This kind of variable can also be used in the loadscript. The next time you are wishing for a user-defined function you can use in the loadscript, see if this concept will do the trick.

* * *

Loading Scientific or E-Notation

2011-01-14T16:51:00.005-05:00

I was loading data into QlikView from a large, comma-delimited text file last week. I found that some of the numeric quantities were expressed in scientific notation or E-notation. Apparently, the person who had prepared the data used a reporting tool that converted some of the very large and very small numbers into scientific notation. The scientific notation numbers contain two numbers separated by an “E” character. The notation means to take the number on the left of the value and multiply by ten raised to the power of the number on the right of the value. For example, the E-notation value of 6.02E23 means that the numeric value is 6.02 times 10 to the 23rd power. It isn’t difficult to deal with in a loadscript but this example might save you some work someday. The loadscript looked something like this:

PROMO_HIST:

LOAD MATERIAL,

WHSE,

If(index(upper(FQTY),'E')>0,

subfield(upper(FQTY),'E',1)*pow(10,subfield(upper(FQTY),'E',2)),

FQTY) as FQTY

FROM D:\Tim\Promo.csv (ansi,txt,delimiter is ',',embedded labels,msq);

That IF statement in the loadscript converted any e-notation values in the data into numbers and for regular numeric values loaded them as usual.

Later in the day, I needed to load the same data into an Oracle table using Sql Loader. Interesting that the idea is the same but the syntax is different. This is what the entry in the SqlLdr control file looked like:

FQTY "DECODE(INSTR(UPPER(:FQTY),'E'),0,:FQTY, SUBSTR(:FQTY,1,INSTR(UPPER(:FQTY),'E')-1) * POWER(10,SUBSTR(:FQTY,INSTR(UPPER(:FQTY),'E'))+1))",

★ ★ ★

Time interval between two timestamps

2010-11-01T16:47:00.008-04:00

I built a report that loads a database table of logged event data. The database table includes a datestamp field containing the date and time of the event. I wanted my report to have a chart showing the duration in hours, minutes, and seconds between the events shown on each row of the chart. That would allow me to make selections on types of events or the source of the event and the time duration between listed events would be useful.

I created a straight-table chart with a dimension of a field called EVTLOG_ID. That single field is a unique identifier that I could sort by descending value and guarantee that the rows in my chart are in chronological order with the most recent events at the top of the chart. Then I added an expression that simply said =EVTLOG_DATE to show the timestamp field from the table. Then I added another expression like this =time(interval([EVTLOG_DATE]) - below([EVTLOG_DATE])),'h:mm:ss') to show the interval between the timestamp field and the timestamp field on the lower line in the chart. The outer function, time, makes the format show as hours, minutes, and seconds without the unneeded "AM" label.

The chart works great – figuring out the elapsed time between timestamps is a tiresome chore to do in your head.

★ ★ ★

Easy Text Search

2010-10-17T20:51:00.003-04:00

People like the feature in QlikView that lets you start typing a word or phrase on top of a listbox (or the pull-down for a particular field on a multibox) and QlikView automatically shows you a list of the fields that contain that word or phrase. Notice that when you begin typing, a small box appears that contains the word you typed in between two asterisks. For example, if you click on the listbox for product names and start typing the word “cheese” a box appears containing *cheese*
The feature is called a text search and the asterisks are wildcard characters. The asterisk represents any string of characters… so the list of values that QlikView is showing you in our example is any group of characters, followed by “cheese”, and followed again by any group of characters… which really means any value containing the letters “cheese”. If you wanted only product names that begin with the word cheese then remove that first asterisk (so that the box contains cheese* ) and you will see only product names beginning with the word “cheese”. Similarly, remove the second asterisk and you only see product names that end with the word “cheese”.

Another wildcard character you can use is a question mark, “?”. The question mark represents any single character. I use it all the time when the field is a date field in the form of mm/dd/yyyy… then , for example, I can type something like 10/??/2010 and it will show me a list of all of the date values in October 2010 (“10/01/2010”,”10/02/2010”, etc.)

(Author's note: Nov 4, 2010. With QlikView version 10 the default behavior of the listbox is not to automatically insert wildcard asterisks as you start to type text. But, the search behavior is very similar and you can still type your own wildcard characters as needed.)

★ ★ ★

Selecting Null Values

2010-10-02T19:42:00.008-04:00

It’s true that you cannot select the null values of a field. But, you can usually do something that will work even better to identify the null values. The idea is that you select values of a different field that are associated with the null values you’re interested in. As always, to help communicate the idea, here’s an example:

Imagine that you have a report of sales data but there’s a defect in the data and some of the products don’t have a product description. You can’t make a selection on missing or null values in the product description field, named PROD_DESC for our example, but you can easily select the products that have a missing or null product description. Go to the listbox for the PRODUCT_ID field or click the pull-down arrow for PRODUCT_ID from a multibox. Now, start typing this expression:

=isnull(PROD_DESC)

click Enter to finish the selection and you now have a selection consisting only of products that are missing their descriptions.

You can use a slightly different expression to select products with a blank or null product description; type this instead =isnull(trim(PROD_DESC))

This technique is useful if you use QlikView to find data quality issues or to validate data from suppliers or customers.

★ ★ ★

Random Sample Selection

2010-09-20T18:46:00.009-04:00

Here’s an idea that will certainly come in handy some day. You can make a selection of random values from any field in your QlikView document. For example, imagine that you have a document loaded with your company’s employee expense report data for the past five years. And, imagine that you want to select a random twenty percent of the employees. Just click on a listbox of employee names or click on a pulldown arrow of employee names on a multibox. Then, type this expression:
=rand()<=0.20

And you will be looking at a random selection of approximately twenty percent of the company employees. Hit Enter to complete the selection.

This idea can work with any field in the document. You can also use an advanced search dialog box instead of a listbox or multibox. This kind of search expression works within the existing selections – if you had already selected only the part of your company operating in Mexico then your expression will select a random twenty percent of employees in Mexico. If you already have some employee names selected then you might want to clear the selection before using the expression. And, like I wrote in my previous blog posting, you can save an expression like this in a bookmark.

Note: If you are going to be doing this random selection several times on the same document then it may give you the same group of values each time. To make sure you get a different set of values every time you use the expression type this slightly longer expression instead:

=rand()<=0.2+now()*0

(See the comments section below for a suggestion about how to do a random sample selection as the Calculated Dimension in a Chart)

★ ★ ★

Bookmarks for Dynamic Selection

2010-09-16T16:04:00.009-04:00

I want to tell you about one of the cool things about doing a QlikView selection using a search expression. I’ve written about this topic before (Click here to read it). When you’ve made the selection with a search expression and then create a bookmark, the bookmark stores the expression and not just a static list of selected values. Then, the next time you click on that bookmark it re-applies the expression!

For example, if you click on a listbox of Salespersons and type =rank(sum(SlsHist))<=10 that will select the top ten Salespersons based on sales history (assuming that SlsHist is your field name for sales history). Then, create a bookmark. This will work best if the selection of Salespersons is the only selection currently made on the document. Now, every time you click on that bookmark it will select the top ten Salespersons. This works within the current set of selections, so, if you’ve already selected only products sold in Canada then when you click the bookmark it will select the top ten Salespersons of products sold in Canada.

You can export bookmarks, of course, and share them with others. I’ve started a small library of these search expression bookmarks that are useful for finding various data issues that need research. I just export the bookmark to a file whenever I create a new one that might be useful again someday.

If you click on one of these bookmarks and nothing gets selected then it means that nothing in the document satisfies the expression criteria.

Now, another cool thing: Beginning with QlikView version 9.0 you can create an Action that applies a bookmark. So, you could define an action that applies one of your search expression bookmarks and apply the Action to a Button or to the On Open event for the document. Using this technique you could make your document do a special selection whenever the document is opened (without writing any macros). Maybe you could make your executive dashboard show the ten most profitable customers over the past week whenever the document is opened.
.
Notes on how to write those expressions:

These are expressions that QlikView evaluates as either true or false. If you have a listbox of your company's Products and you type an equal sign, = , and then the expression; QlikView will evaluate the expression for each Product. If the result of the expression is true then the Product is selected, if the result is false then the product is not selected.

For example, you might type =count(distinct repair_orders)>100 on your listbox of Products and QlikView will select Products with more than 100 repair orders in the report data.
You might type =sum(invoice)<0 to find Products with invoices with negative amounts that need to be investigated. Or, on a listbox of hospital treatment dates type =sum(admissions)>sum(discharges) to find dates where more patients were admitted than sent home.

Mastering this technique for making selections will help make QlikView even more useful to you.

If you have any favorite or especially creative ways to use this technique please describe them in a blog comment (see below).

★ ★ ★

Listbox or Multibox for Group of Things That Satisfy a Condition

2010-09-01T16:06:00.007-04:00

The expressions that I wrote about in my last blog post can also be used in a listbox or multibox. For example, the expression
=If(aggr(rank(sum(SHIPMENTS)),ITEM)<=15,ITEM,null()) which lists the 15 top ITEMs by shipment volume, can be put into a listbox where it will look like a regular listbox for the ITEM field except that it will only show the top 15 ITEMs. [in this example the field SHIPMENTS is the number of cases of product shipped and ITEM is the product or material name]

If you have never used an expression in a listbox before, then go to the listbox properties and open the General tab and scroll up to the very top of the list of fields, there you’ll see the choice for entering an expression.

One thing different here from the kind of listbox that lists a field is that this listbox with an expression will change as your selections change. For our example, it will list the top 15 frozen items when you have frozen products selected but when you change your selection to coffee products it will list the top 15 coffee items. Another difference is that you cannot change the listbox sort option to sort the list by State (meaning the state of whether a thing is selected or not) but you can make the listbox sort things by numeric or character value.

You can set up a multibox with several of these expressions. I have a report with a multibox that has several choices for someone who is interested in finding data problems. Similar to the expressions in the last blog posting, I have =If(sum(SHIPMENTS)<=0,ITEM,null()) that can show me ITEMs with no shipments and =If(sum(SHIPMENTS)<=0 and sum(FORECAST)>0,ITEM,null()) to show me a list of ITEMs with a sales forecast but no shipments, and =If(sum(PROMOTION)>0,ITEM,null()) to show me a list of ITEMs with special promotional forecasts.

So, this is another option for you if you or your report users if you regularly have to select a special group of things; the special groups can be defined in a listbox or multibox or as a calculated dimension as explained in the previous blog post. In my next posting I'll describe how to select a group of things that satisfy a condition using a bookmark.

★ ★ ★

Calculated Dimensions to Show Things Satisfying a Condition

2010-08-23T15:52:00.013-04:00

We often use a Cycle Group for the dimension on our charts. That allows the report user to switch between different dimensions easily. I’ve found it useful to add a few extra calculated dimensions that only show a particular group of things – a group that is useful to the report user or a group that requires attention.
For example, I often include a calculated dimension that only shows the top 15 items by shipment volume (it could just as easily be the top fifteen salespersons, or top 15 most requested service kits, or whatever). The expression for a calculated dimension like that is:
=If(aggr(rank(sum(SHIPMENTS)),ITEM)<=15,ITEM,null())

You will need to check off the Suppress When Value Is Null button on the Dimension tab of the chart properties. If it is important to you for the totals in the chart to remain correct for the entire population then change the code slightly so that the dimension shows the top 15 items and one choice for all other items something like this:
=If(aggr(rank(sum(SHIPMENTS)),ITEM)<=15,ITEM, 'All Others')

With a chart dimension like that the chart changes as the report user changes the selection. When the selection is Country=FRANCE, for example, the chart shows the top 15 items sold in France, and if the selection is Product=CLOTHING then the chart changes to show the 15 clothing items based on shipment volume. If the report user wants to make a selection for the 15 items then that is as simple as dragging the cursor over the 15 items listed in the chart.

In my job we are always looking for things that adversely affect the quality of sales forecasts. One example of something like that are items on the forecast reports that have no shipments. Unless the item is a very new product it should have shipments. When there’s no shipments for a forecasted item that often is a clue that there is a data problem. A calculated dimension for items with no shipments might look like this:
=If(sum(SHIPMENTS)<=0,ITEM,null())

A more specific calculated dimension expression that looks for items with a forecast but no shipments might look like this:

=If(sum(SHIPMENTS)<=0 and sum(FORECAST)>0,ITEM,null())

Similar calculated dimensions might look for items with no sales orders or no bill-of-materials or missing pricing data, etc.

Consider using a calculated dimension when your report users often have to review data for a particular group of things. The calculated dimension may be able to easily isolate a group of things that satisfy a condition even when doing the same task as a selection is complex.

In my next blog post I'll describe how to show and select a group of things satisfying a condition with a listbox or multibox.

★ ★ ★

Export Data From QlikView Table

2010-07-23T11:34:00.007-04:00

If you have a QlikView document already loaded with data you can export the data from any of the QlikView tables inside the document to a file. The file can either be a comma-delimited text file or a .qvd file.

From the menu, choose File->Edit Script to open up the loadscript edit window. Insert a few blank lines at the top of the loadscript. Now, type in a Store command and an Exit Script command. For example, if the table you want to export is named 2009_HISTORY then you might type:
Store 2009_HISTORY into exported_2009_history.txt (txt);
Exit Script;

Don’t save the document with the loadscript changes. It isn’t necessary. Now, from the menu choose File->Partial Reload. The partial reload will execute the store command and then exit. On a large table this might take a few minutes to write out the file.

If you wanted to export the data into a QlikView .qvd file then use (qvd) on that Store command instead of (txt). Once the file has been exported it is easy to load the qvd file into a different QlikView document.

If you only wanted to export a few fields from the table then the fields can be specified. For example, to export just a few fields from our example table we could code:
Store Material, Customer, Invoice_number from 2009_HISTORY into exported_history.txt (txt);

You can export data to a flat file from a table box, too, of course. But, sometimes, when you have a large table you can't display the entire table in a table box and this partial reload method would be your only option. The other problem with a table box is that it doesn't really display all of the rows from a table; it shows the field values whether they come from a single QlikView table or not. This partial reload method will export data from a single table only.

★ ★ ★

Please check off one of the feedback boxes:

Select Top Ten ITEM Values

2010-07-14T18:34:00.008-04:00

I've mentioned several times the kind of expression that you can enter into a listbox or multibox to make QlikView do more complex selections than just clicking on values. Those expressions that you begin with an equal sign, =, are actually advanced search expressions and they can be very useful. Here's one more that helps answer a question that I get many times:

If you want to select the top ten or top twenty things based on a specific criteria there is a way to do it. For example, if you have a product identifier named ITEM and you have a sales history quantity named SHist then you can select the top ten ITEM values based on the highest total sales like this - first click on the ITEM listbox to activate it or click the down arrow for ITEM on your multibox. Then start typing this expression:
=rank(sum(SHist))<=10 and hit enter and it will have selected the top ten ITEM values based on sales history.

I'll have more to say about showing and selecting a group of things that satisfy a condition in my blog postings in August and September.

★ ★ ★

Stepping a Selection Through Each Value of a Field

2010-07-09T14:20:00.005-04:00

Two years ago I posted an entry in this blog describing a macro that would let you step through all of the values of a field. The macro would successively select each value of a field. Shortly after I posted that entry the need for it became obsolete. QlikView has a much better method for stepping through the values of a field and it’s much easier to use than a macro.

Choose a listbox for a field that has the values you want to step through. Select one of the values. Now, just click the Down Arrow on your keyboard. QlikView moves the selection down to the next value. Every time you press the down arrow the selection moves down to the next value. Try it now – it’s easy! You would use this technique while watching the graphs and charts on your QlikView document and see the changes in the graphs and charts as the selection changes. You can quickly step through dozens of different products, or sales regions, or whatever you’re interested in. When you use this technique to step through date values it becomes a way to animate the charts and show behavior of data over time. The up arrow on your keyboard moves the selection the other way.

This is a case where the listbox works better than a multibox. You can click the drop-down arrow for a field on a multibox that contains a selection and then click the down arrow and it will move the selection, but then the list of values closes and you have to click the pull-down arrow again to get it back.

Now, try selecting several values at once on the list box… and then click the down arrow. The group of selections all move down one value. Now, try pressing the Page Down key on your keyboard and the group of selections move down several values. If this was a field of dates and you had selected seven dates then pressing Page Down would move down an entire week. The values that you select don’t need to be contiguous values and the technique still works.
Sometimes you might need to lock some of the other selections in order to better control the values that you step through.

★ ★ ★

[Please click on one of the feedback boxes below to let me know if you find this kind of information useful. Thanks!]

Fire a Trigger When User Changes an Input Field

2010-07-06T13:56:00.009-04:00

Prior to the introduction of Input Fields there was no need for a trigger that would fire when loaded data changes. Because the only way that data could change was to reload it. But, when the QlikView document uses input fields then some of the data can be changed by the report user. The input fields feature allows data to be changed in list boxes, table boxes and some charts and can be very useful.

I helped advise a project that needed to know when data was changed by the report user because they had to apply custom logic for splitting the change from a summary level to detailed level. The solution was to use a variable containing an expression that looked something like this =Sum(sales_forecast) Then, a trigger was defined that would fire when the value of the variable changes. QlikView would evaluate the expression when the data changed and the trigger would fire causing a macro to execute which did the customized data split.

That solution worked well with QlikView 9.0 SR2 but when SR3 was installed on the computer then the trigger no longer fired. Without that trigger there is no way to run a macro when the data changes (if anyone knows of another way to fire the trigger when input field data changes please leave a comment below). The project is currently using 9.0 SR2 and everything works fine but they cannot upgrade to a later release unless this issue is solved for later releases.

[October 4, 2010 Note: The problem seems to be fixed in version 9.0 SR 6]

★ ★ ★

Activate Multiple Listboxes

2010-06-27T21:18:00.007-04:00

When I'm teaching new users about QlikView I always demonstrate how you can click on a listbox to activate it and then start typing a few characters and the values of that field containing those characters are displayed and can be easily selected. I also show them how if you start typing an expression like =Sum(Sales)>10000 it can help display and select products (or regions or salespersons or whatever) with sales greater than 10,000.

Clicking on the particular listbox to activate it is a simple but important part of the process. It is possible to activate more than one listbox at a time. And, if you do that, and start typing characters then QlikView will display the values containing those characters for all of the active listbox objects. You might not be able to hit enter and do the selections for all of the listbox fields at once because they will often interfere with each other.

The way you would activate several listbox objects at once is to click your mouse on the screen and holding the mouse button drag a "box" around the objects you want to activate. Or, you can activate all objects on the screen by pressing Ctrl-A. This feature may be useful when you are selecting multiple field values to match how they are selected in a document bookmark without implementing the entire bookmark (see the previous blog posting from June 15).

★ ★ ★

Restoring the selection for a single field from a bookmark

2010-06-15T17:05:00.009-04:00

When you create a QlikView Bookmark to save your current selections it saves all of the selections for all of the fields where you’ve made a selection in your document. Often, that’s the reason you’re saving the selections as a bookmark – because it’s a complex set of selections for several fields that would take a while to recreate the next time you need them.

Sometimes, it would be nice to restore the selection from a bookmark for just one field. For example, if you have a bookmark named Project_3 that was used to save a complex selection from your monthly sales report, you might want to use only the part of that bookmark that is a selection on the Ship_Date field.

The way you would do that is to click on a listbox in your document for the Ship_Date field or click on the Ship_Date row in a multibox to activate the object. Then, start typing, make sure you begin typing with an equal sign (so that QlikView knows that you are beginning to type an expression). Type this:
=sum( { Project_3 } 1 )

That expression when typed into the selection box for Ship_Date will select only the Ship_Date values that are saved within the Project_3 bookmark. An expression like that can also be used with the Advanced Selection dialog box.

Remember that bookmarks can be exported and imported into other documents. You can even import bookmarks into documents quite different from the original document where the bookmark was created; when you do that the bookmark selections will be applied only to the fields named exactly the same as in the original document.

★ ★ ★

User Option done as a Field Selection

2010-06-11T13:15:00.011-04:00

I’ve prepared a number of reports where the person who opens the report must make a choice about something that affects the report functionality or something that affects the way the report objects look. There are several ways to approach that issue when you design a report. Here’s an example from a recent report I designed.

The report had a chart in it that could show data by various units of measure or “UOM”. The unit of measure could be Cases or Eaches or Dollars and the chart object expressions would handle the conversions. We decided that we’d like the user to be able to change the UOM selection in a Multi-box on the tab so that the user could make all of her selections in one place. UOM doesn’t actually appear in the data being loaded so I had to put a little in-line table into the loadscript that defined the three values. It looked like this:
UOM_SELECTION:
Load * Inline
[UOM
Cases
Eaches
Dollars];

Then I loaded a new set of data into the report. Now, I could add the UOM field to the multi-box and the user could treat it the same as any other selection.

The Multi-box object has a feature on the Presentation tab that works well with this kind of field. If you first select one of the values of the field you can open up the Multi-box Properties dialog and choose the Presentation tab and click the Always One Selected Value check box (If you don’t first select a single value then the checkbox will be grayed out). This helps the user avoid making a mistake with the UOM selection – the user is prevented from clearing the selection in that field and prevented from selecting more than one value. Since the UOM field selection is only for one value, an expression in the chart can refer to it simply by name; for example, the expression might say If(UOM='Dollars',sum(sales)*unit_cost)

I made two other changes to the chart just to make sure that there would be no confusion about the UOM value:
I opened the Chart Properties dialog for the chart selected the General tab and put this expression into the Calculation Condition:
=if(count(distinct UOM)=1, 1, 0)
That will check to make sure that one and only one value of UOM is selected otherwise the chart will show an error message. Then I modified the error message text from the default value of “Calculation condition unfulfilled” to a more useful message that said “Please select a single UOM value to see this chart”.

★ ★ ★

Combining Bookmarks

2010-06-02T20:19:00.008-04:00

I was called to help with a situation last week where one person, working on a pricing project, had analyzed some data and identified a group of materials that would be needed later. The materials were selected and a bookmark was defined.

There was a second person working on a seemingly unrelated marketing project, who also identified a group of materials, selected them, and defined a bookmark for the selection.
The problem was that there was a need to combine the materials selections from the two separate bookmarks. There’s no good way to really combine two entire bookmarks and create a third bookmark out of them. But, when you’re interested in the selections for a single field as the people in my example were interested in the materials field, then there is a technique for combining the selections.

To illustrate the technique, assume that the first bookmark is named BK1 and the second bookmark is named BK2. First, get both of the bookmarks into the same document. An easy way to do that is export the bookmarks from the documents where they were created and then import one or both of them into a second document. In the document containing both bookmarks you will need a listbox for the materials field or a multibox that contains the materials field.
Click on the materials listbox or on the little black triangle next to the materials on the multibox. Then, begin to type this expression:

=sum( { BK1 + BK2 } 1 )

As soon as you type the equal sign, QlikView knows that you are entering an expression. It looks like an expression you might use in a chart but we’re going to use it here to select materials. The syntax is a set analysis expression – it can be a little tricky with the parentheses and curly braces but it is worth the trouble when you need it. As you type the expression a number of materials will be listed with a white background – those represent a combination of all of the materials from both of the bookmarks. Finish the expression by hitting the Enter key and the materials are now selected and show up with a green background.

A similar expression could be used to combine the materials from a bookmark with the currently selected materials in a document. For that you would follow the instructions as above but type this expression:

=sum( { $ + BK1 } 1 )

If you wanted to select only the materials that appears in both bookmarks (the intersection), then type this expression:

=sum( { BK1 * BK2 } 1 )

And, finally, if you wanted to select the materials that are both in your current selection and in the bookmark then type this expression:

=sum( { $ * BK1 } 1 )

★ ★ ★

The Case of the Disappearing Object

2010-06-01T22:15:00.005-04:00

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.

★ ★ ★

Load from Excel even if you don't know the tab name

2009-06-18T15:58:00.005-04:00

Several months ago a reader asked if there was any way for a QlikView document to know the names of the tabs or sheets within an Excel spreadsheet without using VBA or macro code. Well, I still don't know the answer to that question. But, there is a way for a loadscript to load data from an Excel spreadsheet without knowing the tab or sheet name -- it just loads from the "first" or leftmost tab if you don't specify the table parameter.

For example, these lines in your loadscript will load from the first tab of an Excel spreadsheet named aaadata.xls:

XLTBL:
LOAD *
FROM [aaadata.xls] (biff, embedded labels);

When I have time I'll see if I can combine that with a macro that does a browse-for-file dialog and also automatically builds a table-box object and then we'll have a general purpose QlikView document that loads almost any spreadsheet in a useful way.

Make sure to read the comment from Rob Wunderlich. Thanks, Rob!

Test if QlikView table exists within the loadscript

2009-06-17T16:23:00.002-04:00

Sometimes, within the loadscript, I need to check if a QlikView table exists or not. Most often this is done after bringing in a separate QlikView document's data with a BINARY statement and I don't know exactly which tables are included in the older document. Sometimes I just need to check if a QlikView table was successfully created earlier in the loadscript.

Here's the way I do it:

Let RowCount = NoOfRows('TABLE_1');
If '$(RowCount)' = '' then
  //Put statements here to be
  //used if TABLE_1 does not exist

Else
  //Put statements here to be
  //used if TABLE_1 exists

  End If

There are probably better ways to check to see if a QlikView table already exists. If you know another way to check on QlikView tables using loadscript code, please leave a comment. Thanks!

Set analysis and chart dimension

2009-06-16T11:05:00.005-04:00

Using set analysis syntax in a chart expression can be useful but remember that set analysis is based on the concept of selection -- it still must work within the dimension of the chart.
For example, if you have a document where HFcst and Lag are two fields, you might have a chart expression that looks like this:
Sum({$<Lag={2}>} HFcst)

That expression is a sum of HFcst with the set analysis syntax working like a selection override specifying that within the Sum function it should use the selection of Lag=2. You couldn't use that expression in a chart where Lag was one of the dimensions and have it work the way you might expect because the expression still must respect the dimensions of the chart -- each row of the chart must match a particular dimension value. But, as long as Lag is not used as the chart dimension then the expression could be very useful (perhaps as a comparison to the current Lag selection).

Quick load of max field value

2009-06-14T18:26:00.002-04:00

Sometimes, after loading a big table from several different sources I need to find the largest value of a field, or the smallest, or the average, etc. If the name of the big table is BIGTABLE and the field where I want to find the largest value is FDate then I could do something like this in the loadscript:

MAX_FDATE:
Load Max(FDate) resident BIGTABLE;

Depending on the size of BIGTABLE that could take a long time. There's a faster way - the first step is to build a table that contains all of the values of FDate. That looks like this:

ALL_FDATE:
Load FieldValue('FDate',IterNo()) as FDate
AutoGenerate(1)
While not Isnull(FieldValue('FDate',IterNo()));

That will load all of the existing distinct values of FDate almost instantaneously. It really is all of the values of FDate from all tables loaded up to that point in the loadscript and not just BIGTABLE so be aware of that if you use this method.
The second step is just to load the max FDate from our smaller ALL_FDATE table which should run much faster than loading from the resident BIG_TABLE:

MAX_FDATE:
Load Max(FDate) resident ALL_FDATE;
Drop table ALL_FDATE; //no longer needed

Browse-for-File Macro Button

2009-03-14T11:33:00.004-04:00

Several times I’ve created QlikView reports that must read data from a file but the name of the file can vary and must be supplied by the report user. Often this is a file that will be used by the loadscript so the user first clicks a macro button that runs a browse-for-file dialog and then the user clicks the standard Reload button.

Here’s an example of a macro that borrows the browse file dialog method from the Microsoft Excel object model. This macro should work on any PC that has MS Excel installed (it does not actually start Excel). You can copy the code from here and paste it into your QlikView report module code:

Sub Browse_for_File
'------------------
' Get the file pathname
Set oXL=CreateObject("Excel.Application")
f_name=oXL.GetOpenFilename("All Files (*.*),*.*",,"Select file",False)
If f_name="False" then
  'user cancelled out of dialog box
  Set oXL=nothing
  Exit sub
End If
'store file pathname in the file_pathname variable
ret=ActiveDocument.GetVariable("file_pathname").SetContent(f_name,false)
Set oXL=nothing
End Sub

This macro stores the file pathname for the file selected by the report user into a document variable named file_pathname (create the new document variable before running the macro). When I create a report that uses this macro I usually also put an input box in the report that shows the file pathname stored in the variable and allows the user an alternative method for specifying the pathname.

A QlikView document example of this technique, named BIG_Text_File.qvw, is available here – http://finmagic.0catch.com/ Sorry about the ads and popups – just click on the specific file download.

Finding Missing Dates With a Chart

2009-03-09T16:41:00.002-04:00

Yesterday I was reviewing a report of sales and shipment data for 2007 and 2008. The data was very detailed with order numbers and quantities and shipdates. I wanted to know if any shipdates were missing (dates for which no shipping occurred) or if there were really shipments for every day in 2007 and 2008. This is how I looked for any missing dates:

I created a straight table chart with SHIPDATE as the dimension. I created two expressions. One expression, labeled Missing Date, looked like this:

If(Not IsNull(Above(SHIPDATE)),If(SHIPDATE-Above(SHIPDATE)<>1,Date(Above(SHIPDATE)+1)))

And the other expression, labeled Missing Date Count, looked like this:

If(Not IsNull(Above(SHIPDATE)),If(SHIPDATE-Above(SHIPDATE)<>1,(SHIPDATE-Above(SHIPDATE))-1))

The Chart Above() function lets the expression look at a value from the row above. Since the chart is sorted by SHIPDATE, the SHIPDATE value in the current row should be one day greater than the row above. If it isn’t then there is at least one missing date. The check on IsNull is required because there is no row above for the first row on the chart.

The chart worked ok. I tried it out on a sample of the data where I had intentionally excluded two dates from loading in the loadscript and it worked fine.
The total line for the Missing Date Count tells me if there are any missing dates. With this method, though, you have to scroll through down through the chart to find the specific missing dates. You can’t sort the chart to bring missing dates to the top because that would interfere with the Above() function in the expression (I disabled the interactive sort capability for this chart to make sure nobody tried changing the sorted order). The other drawback is that a gap of several consecutive missing dates only shows the one missing date that begins the gap (the Missing Date Count though shows a count of how many dates are missing in that gap).

This expression works in a chart with more dimensions too. For example, the dimensions might be ship-to-country and shipdate. Then the chart would show missing shipping dates (or dates where no shipping occurred) by ship-to-country. It only makes sense though to look for missing shipping dates within data where there is an assumption about shipping every day or every week day in order that your assumptions are tested versus the data.

I know that there are methods that could be used in the loadscript to look for missing dates and there are sql queries that can look for missing members of a sequence in the database. But, if anyone knows of a better method for finding missing dates using only QlikView sheet objects I’d be interested to know how.

Using QlikView to Look at a Big Text File

2009-03-08T12:19:00.009-04:00

This week I was loading data from a very large text file into a database and had some problems with some of the records. I needed to look at the group of records in the file that was causing the problem but the file was big, over 12 million records, too big for Notepad or Wordpad. I decided to load it into QlikView – not the most elegant use for QlikView but setting it up was fast and it worked out better than I hoped.

I put these lines into the loadscript:

BIGFILE:
Load RecNo() as recordno,
Len(@1) as recordlen,
@1 as RECORD
FROM E:\Data\bigtextfile.dat (ansi, txt, delimiter is '`', no labels)
where RecNo()<1000000;>

That RecNo() function in the Where clause limited the load to the first million records. I tried loading all 12 million rows but on my laptop with 2GB of memory the loaded report was pretty sluggish. I defined the backtick character as a delimiter even though it doesn’t appear in the data so I could load the entire record by calling it field @1.

The main tab on the report itself was a table box for all three fields and list boxes for each of the three fields. Selecting by recordno let me look at specific sections of the file. The list box for RECORD was very useful – all I had to do was type a few characters of the data I was looking for and hit the Enter key to select it and QlikView quickly showed me what I needed from the file.

It worked so well that I added a browse-for-the-file macro and changed the load statement to use variables that are defined with input boxes on the report to make it a useful, general purpose report for whenever I need to examine a text file. An example of this document, named BIG_Text_File.qvw, is available here – http://finmagic.0catch.com/ Sorry about the ads and popups – just click on the specific file download.

Report Tells User ABOUT The Data

2009-02-22T08:51:00.008-05:00

Last week a coworker, who was working on a report to validate some data sent to us by a client, asked me how to highlight null values in a chart in the report. I showed her how to do that (Click here to read it). And I offered another suggestion: you don’t want your report user to have to scroll through a lot of data or have to sort the chart to find out if any null values exist; it will help the report user if you provide a text box that tells right away if any nulls or zeros or whatever other interesting conditions exist in the data. Then, the report user can decide how to use the other objects on the report to analyze the data.

This report was a data validation report but the idea can be used for executive summaries and other kinds of reports. The goal is to have a text object that uses easy-to-understand language to tell the report user about important aspects of the data. The idea is similar to the discussion of putting counts in a chart title from a few months ago (Click here to read it). When you design a text object like this you should ask yourself: “what things will the report user be looking for in this report?” and “what questions will the report user be asking of the data?” The text box is telling the report user important things About the data not just summarizing the data.

Here’s what we tried out in the text box of a draft version of that report:

='Current Selection Data Issues:
'&
If(sum(If(Len(Trim(product))=0,1,0))>0,'Data contains null product values.
')&
If(sum(If(Len(Trim(ship_loc))=0,1,0))>0,'Data contains null ship_loc values.
')&
If(sum(If(Len(Trim(mkt_channel))=0,1,0))>0,'Data contains null mkt_channel values.
')&
If(sum(If(Len(Trim(ship_loc))>0 and Len(Trim(ship_loc))<>4,1,0))>0,'Data contains wrong length (not 4) ship_loc values.
')&
If(sum(If(qty<0,1,0))>0,'Data contains negative qty.
')&
If(sum(If(qty=0,1,0))>0,'Data contains zero qty.
')&
If(sum(If(qty<>floor(qty),1,0))>0,'Data contains fractional qty.
')&
If(sum(If(Len(Trim(qty))=0,1,0))>0,'Data contains null or blank qty.
')&
If(sum(If(qty>100000,1,0))>0,'Data contains unusually large qty.')

That text expression only shows the messages if the data conditions actually exist. If some important data condition can only be detected during during data loading then consider capturing the important condition in a document variable while the loadscript is running and then use the document variable in the text box.

We also added this little text box of various counts:

='Current Selection Contains:
'&Num(Count(Distinct product),'###,###')&' products
'&Num(Count(Distinct ship_loc),'###,###')&' ship_loc
'&Num(Count(Distinct mkt_channel),'###,###')&' mkt_channels
'&Num(Count(Distinct product&'.'&ship_loc&'.'&mkt_channel),'###,###')&' SKUs (product/ship_loc/mkt_channel)
'&Num(Count(Distinct orderid),'###,###')&' orders
'&Num(Count(Distinct shipdate),'###,###')&' shipdates, '&min(shipdate)&' to '&max(shipdate)

The data issues text box could also have been written to show counts of the various issues.

Another interesting way to use text expressions like that is in a straight table chart. You can set up the chart with a dimension like shipping-location and put the text expression (minus that first line which is a title) in the chart expressions and then you have a chart that uses easy-to-understand language to tell about data conditions for each shipping-location.

Highlighting a Chart Value

2009-02-21T10:16:00.005-05:00

I was asked, “How do you highlight a null value in a straight-table chart?” If you want to set the background color or text color based on a range of expression values then the Chart Properties->Visual Cues is the way to go. But the Visual Cues tab doesn’t work well for null values and it won’t help if you want to highlight the value based on anything more than a simple test of values. For that, you want to use a separate, special expression to set the background color or text color.

Right-click on the chart and select Properties and go to the Expressions tab. Click on the little plus sign ( + ) to the left of the expression you want to highlight. See the example in the picture above. In the example, the expression is named Shipments and I have clicked on the Background Color choice and entered this expression into the box:
If(IsNull(Shipments),LightRed())

That expression tells the chart to use the LightRed color as a background color when the Shipments value is null. There are other colors you could use – search on color functions in the Help text to find information about other colors. Background color works well to highlight values in a straight table chart but it won't help with line graphs or bar charts. The expression can include other factors like the source of the data or the value in the chart dimension or the average monthly sales volume for 2008; use whatever factors that provide a reason for pointing out that particular expression value.

Capture Variable Values in the Loadscript

2009-02-12T17:53:00.008-05:00

I often put statements in the loadscript that record current conditions as document variables in case the information might be useful someday when checking into a problem or simply to document the origin of the data. Here’s a few that have proved useful:

let reload_time = now();
let user = OSuser();
let computer = ComputerName();
If IsPartialReload() then
let partial_reload_time = now();
Else
let partial_reload_time = '';
End If

Those variables can be displayed on the title tab or in a corner of the main tab.

You can capture information from the data that is being loaded and store it in a variable. This is useful for control information that would be displayed on the title page like the system information above. It can also be useful to control If statements and Loops and Where clauses in the loadscript.

Here’s an example that reads a date from an Oracle table and stores it in a QlikView document variable so it can be displayed in a chart title:

//Find OHPOST date and store in a one-row table
DUNK_OHPOST:
SELECT TO_CHAR(MAX(OHPOST),'MM/DD/YYYY') AS DUNK_OHPOST FROM SKU;

//Now, put the value into a document variable
Let DUNK_OHPOST=Date(Peek('DUNK_OHPOST',0,'DUNK_OHPOST'));
Drop table DUNK_OHPOST;

That Peek function takes 3 arguments: fieldname, row number, and table name. For a one-row table use zero to indicate the first row. If that is a unique field name then you could also use the FieldValue function to retrieve the value from the first field value.

In older QlikView versions you could use the Peek function in a text object but that isn't allowed any longer. So, store the values in document variables and you can display them in text object or chart titles. Consider displaying data values that tell the report user something important about the data; such as earliest or latest posting dates, high or low order numbers, current fiscal period, currency conversion factor, source application version number, security or classification warning, etc.

Version 7.50 Chart Memory Errors

2009-02-11T14:57:00.006-05:00

We have a client who is using QlikView version 7.50. Memory errors on the charts have been a problem ("out of object memory", "allocated memory exceeded") with this version. QlikTech's advice was to upgrade to the more stable version 7.52 but for various reasons that wasn't going to happen. It gradually became clear that the memory issues occur after someone reloads data into the report. Simply saving and closing and then re-opening the report after a reload solves almost all of the chart memory problems we have with this version.

QlikView Exists Function

2009-01-31T21:59:00.006-05:00

One QlikView function I use often in the loadscript is the Exists function. You give it a value or expression and the name of a field and it tells you if the value already has been loaded into the field. The function returns a True or False so it can be used in the Where clause of a Load statement or in an If statement. There are a lot of uses for the function and below are three examples that I’ve used recently. For the first example, I use it near the end of the loadscript when I am loading master data to match data already loaded in previous tables. Here’s what the code might look like when loading a product description to go along with previously loaded product code values:

PRODUCT_MASTER:
Load
PRODUCT_CODE,
PRODUCT
Where Exists(PRODUCT_CODE);
SQL SELECT
PRODUCT_CODE,
PRODUCT_CODE' - 'DESCRIPTION AS PRODUCT
FROM PRODUCT_MASTER;

That piece of code will only load product descriptions for product codes that were already loaded in previous tables. Note that the Exists function is a QlikView function so it is used as part of the QlikView Load statement and not as part of the SQL statement. Only one argument for the function is used in this case since the field name and the value to be checked are the same.

A second example of a place I use the Exists function is when I load temporary tables with special field names and values that fit a particular type or grouping so I can use the values to differentiate values in other fields I’m loading. Here’s an example: I load product codes into a special field that will contain only product codes for products made with 100% recycled materials:

TEMP_RECYCLE_PRODUCTS:
Load
PRODUCT_CODE as RECYCLE_PRODUCT
Resident PRODUCT_MASTER
Where CONTENT='100 POST-CONSUMER RECYCLE';

Now, I can use that field name, RECYCLE_PRODUCT, with the Exists function as I load or process other data. This technique is especially useful when the data is coming from different sources where something like a SQL join is not available. I might use it like this when loading other data:

GREEN_SALES:
//Sales of product made from recycled materials
Load
CUSTOMER,
SALES_DATE,
QUANTITY
Resident 2009_SALES_DETAIL
Where Exists(RECYCLE_PRODUCT,PRODUCT_CODE);

Drop table TEMP_RECYCLE_PRODUCTS;

At the end of the loadscript or when it is no longer needed you should drop temporary tables so that they don't unnecessarily use up memory or create unneeded associations.

In this third example, I use the Exists function to load only the first of a set of identical values as they are read in the input data. For example, this piece of code in the loadscript will only load data for the first time an error message appears in an error message log file. The input from the database is sorted into timestamp order and the Exists function in the Load statement checks to see if the specific error message value has already been loaded:

ERROR_HISTORY:
Load
ERROR_TIMESTAMP,
ERROR_MESSAGE
Where Not Exists(ERROR_MESSAGE);
SQL SELECT
ERROR_TIMESTAMP,
ERROR_MESSAGE
FROM MESSAGES_LOG
ORDER BY ERROR_TIMESTAMP;

Benefits of Concatenating a Code and Description

2009-01-24T11:34:00.007-05:00

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.

Error: fetched column value was truncated

2009-01-22T13:07:00.002-05:00

I got an error yesterday on a QlikView document that had reloaded dozens of times before. It appeared to be an Oracle SQL error. The error message that appeared in the pop-up box during loading said, SQL Error:[Oracle][ODBC][Ora]ORA-01406:fetched column value was truncated

Despite appearing like a SQL error, the piece of SQL code didn’t cause any errors when submitted through Oracle SQL*Plus. It only caused an error when used to load a table in the QlikView document. I was using QlikView version 8.50.6206.5.

With a little trial-and-error I narrowed down the source of the problem to a single database column, named QTY, which was defined in the Oracle database as a FLOAT. Since this document had reloaded successfully in the past, it must be the new data stored in the table that is causing the problem now.

In any case, I was able to make the document load and satisfy the reporting requirements by simply changing the SQL a little from SQL SELECT QTY… to SQL SELECT ROUND(QTY,2)… After this change, the document reloaded with no problem.

I’m hoping this blog entry might help someone fix the problem if it happens to them. And, inasmuch as this blog also serves as my own QlikView notes it might serve as a reminder to myself when it happens again in one of my documents.

(Note the comment describing another experience with this error)

Load a Table With All of the Values for a Field

2009-01-14T15:15:00.005-05:00

An idea for developers of large reports--

Sometimes in the loadscript you need to build a table that contains all of the previously loaded values from the tables that have already been loaded. This situation comes up when a concatenated key has been used in the tables. For example, you might have a report that loads data into several tables from different sources but you’ve loaded each of the tables so that it has a key field comprised of product_code, customer_number, ship_location, and ship_date; all concatenated together but separated with an underscore character. This technique is sometimes used in large reports in order to reduce memory requirements by minimizing the number of synthetic keys that QlikView must create. Each of the large tables shares only one field, the concatenated key field, instead of sharing multiple fields and causing QlikView to build the synthetic keys.

At the bottom of the loadscript you must collect all of the concatenated key values and explode them into the individual fields so that QlikView can build the associations. If the concatenated key from our example is named CCKEY then code like this would load all of the existing CCKEY values:

ALL_CCKEY_TABLE:
Load
FieldValue('CCKEY',IterNo()) as CCKEY
AutoGenerate(1)
While not Isnull(FieldValue('CCKEY',IterNo()));

That will create a table of all CCKEY values. Now add the part that splits out the fields based on the underscore separator character:

Left join load
CCKEY,
subfield(CCKEY,'_',1) as product_code,
subfield(CCKEY,'_',2) as customer_number,
subfield(CCKEY,'_',3) as ship_location,
subfield(CCKEY,'_',4) as ship_date
Resident ALL_CCKEY_TABLE;

A table like this loads very fast since all of the data is already available in memory.

Archive Data

2009-01-10T07:37:00.003-05:00

We sometimes use QlikView as a method for capturing and saving data for archive purposes. These reports aren’t normally used unless someone needs to review historic data or research data to answer a question. For example, if we have a project where we might need to check on what the product master looked like six weeks ago (most companies only care about current master data and they don’t keep track of historic master data), then we might set up a daily job that loads a QlikView document with the product master and any associated data. Then, the document is simply saved in case it is needed. We usually give the document file a filename containing the date to help organize the files.

This advantage of this method over some kind of data dump file is that the method for reviewing the data (QlikView) is automatically part of the process. Anyone who needs to review the data need only click on one of the files in the archive folder. The data in QlikView qvw files is stored in compressed form so no further zipping is needed to conserve disk space.

Adding an Image to the Document

2009-01-06T17:38:00.007-05:00

You can easily add visual interest to your QlikView document with a few lines or colored boxes or graphic elements. Add lines of any thickness or color by selecting from the menu Layout->New Sheet Object->Line/Arrow Object... The object properties are used to adjust the color and appearance of the line or arrow. You can drag the line around your document to get the most logical or appealing placement.

For colored boxes that stand out from the tab or sheet background insert a text object (Layout->New Sheet Object->Text Object). In the object properties choose a background color that works well with the sheet background. Drag the text object around and adjust the size with your mouse. Whether you actually have text in your text object is up to you. The text objects can be overlaid or layered with other sheet objects like charts. This is a common way to add a legend or explanation of the data that will appear alongside a chart. The Layout tab in the object properties has a Layers setting that will help with overlapping objects. The transparency setting can also help with layered objects. Don't hesitate to click the Help button for more information if you see something that you don't understand.

The background of the tab or sheet can be changed by right-clicking on the top of the sheet where the tab label is. Then select Sheet Properties and look for the controls that adjust background color or let you add an image. A background photograph is often too busy for a sheet containing chart objects but it may be perfect for a title sheet. The background and colors of a well-designed website might give you some ideas for how to design the sheets of your document.

The text object can show a diagram or picture. This is an easy way to add a corporate or department or project logo to the document. When you add the text object look at the General tab of the object properties. In the Background area click the Image button and then the Change button (btw- note how QlikView uses lines and boxes to group the logical elements of the properties window… a few lines can make the information on your document easier to understand too). Then you can select a jpg or png file from your computer and insert it into the document. Try it out! Even if you are designing a business document there's no need to be boring. Insert a cartoon image of your boss... but before the document is seen by company management you should make sure the image is respectable and that you or your company own the rights. Consider this method for adding a diagram to your document or adding photos of products or a map of locations.

Text objects can be set up so that they appear or are invisible based on the data. On the text object Layout tab you can click the Conditional button and then enter an expression that will control whether the text box is visible or not. You could make the text object visible based on the person using the report, the loaded data, time of day, or day of the week. Use it to alternate between two or more different text objects with different images. For example, in a corporate dashboard application you could make the image of a sunny day or of a stormy day appear based on the current month's revenue versus last month (or whatever passes for good news versus bad news in your business). The expression should be something that evaluates to a zero or a non-zero value. If the value is zero then the text object is not visible.

See a Title Tab When Report Opens

2008-12-27T10:36:00.004-05:00

Normally, the active sheet or tab a person sees when they open a QlikView document is the sheet that was active when the document was last saved. So, the easiest way to influence which sheet a person sees when they open the document is just to be careful which sheet is active when the document is saved and closed.

You can also control which sheet or tab a person sees when they open a QlikView report with a small macro. We often do this when a report contains a title tab. The title tab contains information about the report. It would show the report title, of course, and could also show the date the data was loaded, databases used, contact info for the author or support person, security label, etc. It is also a great place for a company logo or other identifying graphics.

Step one is to build the tab you want users to see when they open the report.

Second, create a small macro (Settings->Document Properties...->Macros). For example, if the name of the title tab is Title_Tab then the macro would look like this:

Sub Activate_Title_Tab
'---------------------
ActiveDocument.Sheets("Title_Tab").Activate
End sub

Then, associate the macro with the OnOpen document event trigger and click OK.

Now you have a macro that is executed whenever the document is opened and the macro activates the tab that you want the user to see first. The Activate_Title_Tab macro is also available to be associated with a button or other events or executed by other macros when it is appropriate to switch to a particular sheet or tab.

Loading Variables With A Macro

2008-12-14T14:48:00.004-05:00

In order to complete the discussions on the automated loading of QlikView document variables I have to provide an example of loading the variables and values from a file using a QlikView macro. This would be useful for an application that can't load variables as part of the loadscript. If your loadscript needed to have the variables already loaded then you would need to load the variables before beginning the loadscript. Or you might load a group of variables based on something that the report user does to the report, like loading in a set of historical monetary conversion factors in order to do analysis of historical finance data. Here's an example of how it could be done.

This is written as a function but it could also be done as a subroutine. Because this function is part of an application that runs automatically and unattended on a server it has more error-detection than you might ordinarily use. A subroutine that is invoked as a QlikView macro would have to call this function.

Function SET_RPT_VARS()
'----------------------
'Set document variables based on the data in RPT_VARS.csv file.
'File is comma-delimited. First field on each record is variable name.
'Second field is variable value. First record is column labels.
Dim varsfile, varsrec, tempvar, tempvalue, ret, recctr
SET_RPT_VARS="SET_RPT_VARS Error"
recctr=0

On error resume next
Set fso=CreateObject("Scripting.FileSystemObject")
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to create FileSystemObject"
Err.Clear
Exit Function
End If
On error goto 0

If not fso.FileExists("RPT_VARS.csv") then
SET_RPT_VARS="ERROR: RPT_VARS.csv file not found"
Exit Function
End If

On error resume next
Set varsfile=fso.OpenTextFile("RPT_VARS.csv",1,False,0)
If Err.Number <> 0
then SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to OpenTextFile"
Err.Clear
Exit Function
End If
On error goto 0

Do While not varsfile.AtEndofStream 'loop through the file
recctr=recctr+1
On error resume next
varsrec=varsfile.ReadLine
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to ReadLine"
Err.Clear
Exit Function
End If
On error goto 0

If recctr > 1 then
'ignore first record column labels
On error resume next
tempvar=Trim(Left(varsrec,Instr(1,varsrec,",")-1))
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to extract tempvar"
Err.Clear
Exit Function
End If

tempvalue=Trim(Right(varsrec,Len(varsrec)-Instr(1,varsrec,",")))
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to extract tempvalue"
Err.Clear
Exit Function
End If

ret=ActiveDocument.CreateVariable(tempvar)
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to CreateVariable"
Err.Clear
Exit Function
End If

ret=ActiveDocument.GetVariable(tempvar).SetContent(tempvalue,false)
If Err.Number <> 0 then
SET_RPT_VARS="ERROR: SET_RPT_VARS, unable to SetContent"
Err.Clear
Exit Function
End If
On error goto 0

End If

Loop 'end of loop through file

varsfile.Close
SET_RPT_VARS=FormatNumber(recctr-1,0) + " document variables created from RPT_VARS.CSV"
set fso=nothing
set varsfile=nothing
End Function

QlikView Tutorial

2008-12-09T16:09:00.005-05:00

People often come across this blog when they are searching the web for information about how to use QlikView. If you have a working copy of QlikView on your computer then you already have links to excellent information. Start up the QlikView application and look at the start page -- click Resources and you should see something like the picture on the left. Those links will take you to current information about QlikView. There's a lot of basic info but it isn't just for beginners - I never fail to find something new and useful in the QlikTech and QlikCommunity websites.

If you don't have a working copy of QlikView or can't find the start page then follow this link: Click here for QlikView Information

or follow this link to the QlikCommunity: Click here for QlikCommunity

Variables in the .bat script

2008-12-04T18:25:00.005-05:00

This series of blog postings about variables has probably been of interest mostly to technical folks concerned with automating QlikView documents. This one is also like that only more so. Here’s some automation wisdom that you won’t find anywhere else:

We have a set of reports where the document variables contain all the file pathnames and database access names. Loading of the reports is handled by Windows .bat scripts. We saw that the .bat scripts also needed to be able to use flexible file pathnames and database access. The variables and their values are stored in a comma-delimited file (a .csv file). The .bat scripts load their variables from the same file that the QlikView documents use to load their variables.

This is a .bat script command that will load DOS variables from a comma-delimited file:

FOR /F "skip=1 tokens=1-2 delims=," %%a IN ('type ..\Report_Control\RPT_VARS.csv') DO set %%a=%%b

That one line reads a comma-delimited text file named RPT_VARS.csv that is stored in a folder named ..\Report_Control
Skip=1 tells it to skip over the first line of the file (because it contains column titles).
The text file is simple. It looks something like this:

Variable Name,Variable Value
RPT_DBNAME,INVT_PROD
RPT_INPUT,E:\CURRENT\DATA\
RPT_OUTPUT,\\AMSERV28\PROJECTS\
RPT_LOG,C:\LOGS\BATCHLOG.TXT

Within the .bat script the variables are used between percent signs. So a line in the script might look like this:

echo %date%,%time% QV Batch started >> %RPT_LOG%

One of the reasons we stored all of the pathnames and access parameters in an external file that could be turned into variables is that it simplified the change control or promote to production process. We could develop a QlikView report and the associated scripts on a development server and then easily move the report and scripts as files to the QA server or production server without any changes. The programs would run correctly after being promoted to QA or production because they picked up the appropriate pathnames and database access variables info from the comma-delimited file stored on the new server.

We name a lot of our files with a yyyymmdd datestamp in the filename, for example “20081126_NorthRegion.dat”. It’s easy to create today’s yyyymmdd date in the .bat script as a variable with this script line:
FOR /F "tokens=1-4 delims=/- " %%A in ('date/T') do set TTDATE=%%D%%B%%C

Then it can be used in the .bat script as part of a filename like this:
COPY CURRENT.DAT %TTDATE%_NorthRegion.dat

Loading Variables Via Loadscript

2008-12-02T21:41:00.004-05:00

You have two basic alternatives if you want to load document variables from a database or file. You can load them as part of the loadscript as you load data for reporting, or, you can load them through a macro script either within the QlikView document or external to the document.

In order to load variables from a database or file you must first have the variable names and the variable contents defined in the database table or in the file. The file might be a spreadsheet or text file but it must have one column or field defined for the variable names and a second column or field defined for the variable contents.

Within the QlikView loadscript you first load the variable names and contents into a QlikView table. From a database you might code it something like this:

RPT_VARS:
SQL SELECT
VAR_NAME,
VAR_VALUE
FROM REPORT_VARIABLES;

Then insert some code like this to convert the table into document variables:

Let RowCount = NumMax(NoOfRows('RPT_VARS'),0)-1;
For i=0 to '$(RowCount)'
  Let TempVarName = peek('VAR_NAME',$(i),'RPT_VARS');
  Let TempVarValue = peek('VAR_VALUE',$(i),'RPT_VARS');
  Let $(TempVarName) = '$(TempVarValue)';
  next

//If the table is no longer needed it can be dropped
Drop table RPT_VARS;

Note the NumMax function. That is necessary if the RPT_VARS table were ever empty; a null RowCount variable will cause a never-ending loop.

At work we have some reports where all of the expressions and titles and field labels are handled with variables. This allows us to use the same report at several different companies but the appearance and functionality of the report is tailored to local terminology and business practice. Another way to use this concept might allow the same report at a single company to look and work differently when it loads data from different databases that contain different sets of customized variables. So, a standard sales report could look different for the Commercial Division than it does at the Baked Goods Division.

Listing All Variables and Contents

2008-11-26T17:08:00.013-05:00

After last weeks blog posting I got some questions about how you might list out all of the document variables and their contents. It can be a useful thing for reports that depend on variables for their functionality and useful for corporate developers who must document report functionality.

Below is a Microsoft Excel macro that could help you. When you run this macro it will ask you to select a QlikView report file and then it will copy all of the document variables and their contents into the current worksheet. Once the data is in a spreadsheet you can sort it, print it, search it for particular fields or functions, or write it out as a text or tab-delimited file. In a future blog posting I'll show how variables can be loaded into a report from a spreadsheet which, along with the macro below, will give you a method for sharing and maintaining variables.

You can get a copy of the spreadsheet with the macro installed and a button that runs the macro at this website: http://finmagic.0catch.com/ The spreadsheet on the website also includes a button that will list all variables from a currently open QlikView document (sorry about the advertisements on the site - just click on the spreadsheet downloads).
Here's the Excel macro code. You can copy and paste the macro code from this window and put it into your own spreadsheet if you want to build your own.

Sub List_QV_Variables()
'This Excel macro will ask you to select a QlikView Report File and
'then it will put the variable names and contents into your current worksheet
'--Tim Benoit, Nov 2008
Dim oQV, oRpt, oVars, oTempVar, oThisVar, qv_fn

'Get the QlikView report pathname
qv_fn = Application.GetOpenFilename("QlikView Report File (.),*.*", , "Select a QlikView .qvw Report File", False)
If qv_fn = 0 Then
  Exit Sub
  End If

'Write out some heading text
ActiveSheet.Cells(1, 1).Formula = "QlikView Variables List"
ActiveSheet.Cells(2, 1).Formula = "Report pathname:"
ActiveSheet.Cells(2, 2).Formula = qv_fn
ActiveSheet.Cells(3, 1).Formula = "Variable Name"
ActiveSheet.Cells(3, 2).Formula = "Variable Content"

'Open the QlikView report file
Set oQV = CreateObject("QlikTech.QlikView")
Set oRpt = oQV.OpenDoc(qv_fn)
Set oVars = oRpt.GetVariableDescriptions
'Loop through the variables
For i = 0 To oVars.Count - 1
  Set oTempVar = oVars.Item(i)
  varname = Trim(oTempVar.Name)
  Set oThisVar = oRpt.Variables(varname)
  varcontent = oThisVar.GetRawContent
  'Write data into worksheet cells
  ActiveSheet.Cells(i + 4, 1).NumberFormat = "@"
  ActiveSheet.Cells(i + 4, 1).Formula = varname
  ActiveSheet.Cells(i + 4, 2).NumberFormat = "@"
  ActiveSheet.Cells(i + 4, 2).Formula = varcontent
  Next

'Close QlikView
oRpt.CloseDoc
oQV.Quit
End Sub

Copying Variables From One QlikView Report to Another

2008-11-21T20:59:00.008-05:00

Today, one of the guys at work was trying to figure out how to transfer all of the document variables from one QlikView report to another. We discussed some of the things we've done in the past: loading variables and values from a spreadsheet or database either through the loadscript or through macro code. We even have an older application that stores variables in a table with loadscript code and then the report is used as a "binary" in another report's loadscript and the second report turns the table values back into variables. But, none of those options seemed to fit the requirement. One obstacle is that we could not figure out an easy way to export all of the variables and values from a report. You can export the expressions from the Expression Overview but the Variable Overview has no similar function.

With some help from Lars at Brait AB (a reader of this blog, Thanks, Lars!) who suggested a way to loop through the variables, I tested the VBScript below that can transfer variables from one report to another. I created a small text file on my laptop named "tbtest.vbs" and typed this code into it:

' /* Test Moving a variable from one QV report to another */

Option Explicit

Dim objQV, objSource, objDest, objSourceVar, objDestVar

Dim objVars, varcontent, objTempVar, varname, i

'initialize

Set objQV=CreateObject("QlikTech.QlikView")

Set objSource=objQV.OpenDoc("C:\QVfolder\Source_rpt.qvw")

Set objDest=objQV.OpenDoc("C:\QVfolder\Dest_rpt.qvw")
set objVars = objSource.GetVariableDescriptions

'Loop through the variables

for i = 0 to objVars.Count - 1
set objTempVar = objVars.Item(i)

varname=Trim(objTempVar.Name)

Set objSourceVar=objSource.Variables(varname)

varcontent=objSourceVar.GetRawContent

'display the variable to check on progress if needed

msgbox(varname & " = " & varcontent)

'update the value of variable in Destination report

Set objDestVar=objDest.Variables(varname)

If objDestVar is nothing then

'must need to create variable

objDest.CreateVariable varname

Set objDestVar=objDest.Variables(varname)

End If

objDestVar.SetContent varcontent,true

next 'end of loop

'save Destination Report if desired

objDest.Save

'we're done, close down

objSource.CloseDoc

objDest.CloseDoc
objQV.quit
WScript.Quit

In order to run it, I just double-clicked on the tbtest.vbs file on my desktop. You can see the pathname to the two QlikView report files in the code. This script successfully copied the variables and their values from the source report to the destination report.

If you omit the part of the script that opens and updates the destination report you could turn it into a script that lists out all of the report variables and their contents -- something that is not available with the regular variable overview.

Putting a Count in the Chart Title

2008-11-14T16:20:00.010-05:00

In my company we write a lot of QlikView reports to validate data. As part of our software projects we must check data to see that it matches assumptions and expectations. We also check the data against any other source available to validate data accuracy. Often, one of the simpler but important things on those reports is a count of things.

For example, a report might include a table box showing ITEM data. Instead of just showing the word ITEM in the table box title, we put a count of the ITEMs in the title. You can change the title on most sheet objects by right-clicking on the object and choosing Properties. The Title is in the General tab. The title might contain this:
=Num(Count(Distinct ITEM),'###,###') & ' ITEMs'

That gives us a count of the number of ITEMs that is formatted with the NUM function to use a comma as a thousands separator. The title then might show up on the chart as:
  1,220 Items

A List Box for the field named Location might contain this in the title:
=Num(Count(Distinct Location),'###,###') & ' Locations'

For another title you might want the number of sales orders in fiscal 2009. You can add an If test with a text expression like this:
=Num(Count(Distinct If(fiscal_yr='2008',order_id)),'###,###') & ' 2009 Orders'

These counts will change depending upon your current selection. The counts don't necessarily need to be in a title. The object title limits you to a single line of text. You can put the counts into a text box and that will allow you to show the information on multiple lines. If you type this into a text box:

=Num(Count(Distinct ITEM),'###,###') & ' ITEMs
' & Num(Count(Distinct Location),'###,###') & ' Locations
' & Num(Count(Distinct SDATE),'###,###') & ' Shipping Dates'

Note that the end-of-line or carriage return is part of the text. The text box will show up on the report saying something like this:
  1,220 Items
  15 Locations
  52 Shipping Dates

We have a report with a table box of SKU data where SKU ("stock-keeping unit") is a combination of item and location although the item and location fields are separate. The title for that table box contains this text expression that counts the number of occurrences of the concatenated item and location field values:
=Num(Count(Distinct ITEM&'@'&LOC),'###,###') & ' SKUs'

We often use charts with a cycle group in the dimension. That allows the report user to easily switch the chart dimensions. The current setting of the cycle group can be used in the report title if it makes sense. So, for a chart where the cycle group is named "Corp Group", you can make it a part of the chart title like this:
='Sales History by ' & GetCurrentField([Corp Group])

and then the chart title will switch between
  Sales History by Customer
  Sales History by Fiscal Month
  Sales History by Region
as the report user clicks to switch dimensions using the cycle group.

For a slightly more complex example of using the cycle group, consider a chart where the dimension is a cycle group named "Corp Group" and we want to include a count of dimension rows in the title. You could make the chart title like this:
='Sales History - ' & Num(Count(Distinct $(cg_label_1)),'###,###,###') & ' ' & GetCurrentField([Corp Group]) & 's'

You must also define a document variable named cg_label_1 that contains the name of the specific cycle group being used in the dimension. The variable would contain this text:
=GetCurrentField([Corp_Group])

now the chart title will automatically switch between text like this as the cycle group changes:
  Sales History - 4,800 Customers
  Sales History - 12 Fiscal Months
  Sales History - 6 Regions

Set Analysis

2008-11-09T09:44:00.011-05:00

In QlikView version 8.50 there is new functionality called Set Analysis. A "Set" as used in this functionality is similar to the already familiar concept of a QlikView Selection. This functionality allows you to define chart expressions that are calculated using something other than the current selection.

QlikView's Selection capability is already a powerful, flexible capability -- essentially a grouping of data defined any way you choose. With Set Analysis you can compare two or more of these groupings quickly and easily. In the past, this kind of functionality in a QlikView report usually required special handling in the loadscript and table structure. Now, the Set Analysis can be set up using chart expressions and existing data.

I'm sure that I will be writing again about the capabilities of this feature, so today I will only offer two examples of what can be done. These are simple to try out on a report so be sure to try them. Remember though that you will need new version 8.50. You can download the latest version of QlikView from the QlikTech or QlikCommunity websites.

Examples:

Imagine that you have a chart and one of the expressions shows historic inventory quantity. It might look like this:
Sum( qty_inv_hist )

Now, add a new expression next to that first one that will show historic inventory quantity but using the Previous Selection. "Previous Selection" is the selection you would get if you clicked on QlikView's Back button. The expression might look like this:
Sum( {$1} qty_inv_hist )

Note the curly braces that are part of this syntax. You now have a chart that compares data from the current selection to data from the previous selection. This is a very flexible feature. The previous selection might be: data from your top 5 sales regions, data from last month, data from last year, data from the Western states, data from regions where the new product has been introduced, data from products using blue labels, data from customers added in 2005, data from orders shipped by rail, etc. Get it? With little effort you can do kinds of data analysis you might never even have thought of before.

Now, add another expression that will be calculated using a selection stored inside a bookmark. This is what the expression might look like for a bookmark named "TB Item List":
Sum( { "TB Item List" } qty_inv_hist )

Quotes are required around the bookmark name because it contains spaces (you could also use square braces, [ ], instead of quotes). The idea is similar to the expression that used a previous selection except that now the selection used in this expression will be based on the bookmark selection no matter what the current selection is in the report. The bookmark can be changed at any time, of course, and some people might find this feature easier to control than the one based on a previous selection. Using a bookmark is appropriate too for a complex selection that involves making selections from several fields. The idea of a Previous Selection as in the example above works only for the most recent field where you made a selection. If the selection you want to use in the chart expression involves several fields then a bookmark is the way to go. To make this a little easier for report users, you could use a variable in the expression for the bookmark name and give the report users an input box so that they can choose the bookmark to use without opening up the chart expressions. Remember that bookmarks can be exported and shared even among dissimilar reports (Click here to read a little about sharing bookmarks).

The reference manual recommends that these set analysis features not be used on expressions that do calculations using fields from multiple QlikView tables. For example, an expression that multiplies historic inventory by a cost factor should not be used if the cost factor is from a different table within QlikView than the table where historic inventory is stored. Note that is is about where the fields are stored within the QlikView tables - it has nothing to do with the external source tables that were used for loading data. The manual says the results are "unpredictable" -- that's a good enough warning for me... it's like when the waiter says "don't order the fish" - you really will be better off if you take his advice and don't have fish for lunch.

If you are interested in the Set Analysis functionality it will be worth your time to read about it in the version 8.50 reference manual. Post a comment to this blog or send me an email if you think of something unusual or clever you can do with set analysis.

Variable Overview

2008-11-09T08:14:00.006-05:00

I wrote about the Expression Overview in a blog posting last week (Click here to read it) so I feel I must drop the other shoe and mention the Variable Overview that is available in the same menu, Settings->Variable Overview or hold down the Ctrl+Alt+V keys at the same time for a hot-key shortcut.

The Variable Overview window shows two columns: Variable Name and Value. Click on the column headings to sort the list. Click the Add button to add a new variable. If you click on one of the variables to select it, you can remove it with the Remove button or edit the variable value in the Definition box in the bottom of the window. For a long, complex variable you can click the tall button labeled with an elipsis (". . .") to open a full-screen window for editing.

There's no way to print the contents of the window or export it to a file or search through the contents but the Variable Overview is very useful for working with reports that use a lot of variables. There is a Help button but on my copy of QlikView it doesn't sense the context and it opens to the wrong Help window.

There is a method that can be used to list out all of the document variables and their contents (Click here to read about it)

Expression Overview

2008-11-01T21:06:00.003-04:00

If you work with moderately complex reports then you must be working with reports with expressions. Most of the time we're concerned with expressions in chart objects (the graphs or tables with dimensions) but there can be expresssions in many places in a QlikView document. An expression might control whether a tab or chart is visible or not or it might control colors used in a chart. An expression might be used in a text object to provide customized information or a warning, or it might be used in a chart title to provide a customized title. In version 6 the only way to maintain these expressions was to remember where they are and open up each object to check on specific expressions.

In recent releases of QlikView there's a great feature called the Expression Overview. To see it, select Properties->Expression Overview (or, hold down the ctrl key, the alt key and the E key at the same time). This overview shows you where the expressions are in your document, it can show you the text of the expression, it will allow you make changes to the expression, you can search for specific text in the expressions like a field name or variable name, and you can do mass changes with the search and replace functionality.

There's no HELP button but you can make the Expression Overview Help screen appear if you press your F1 button. The Help screen will explain the available functions.

As always, if you are going to make serious changes to a document it's a good idea to make a backup copy of the document first, just in case you make a mistake and want to restore the document back the way it was.

My own preference, when I want to search for a particular kind of expression or if I just want to review all of the expressions in a report looking for problems and opportunities is to use the Export button to export all of the Expression Overview data. A box will appear asking for the pathname to use for the exported text file. The text file is a tab-delimited text file that can be reviewed with Notepad or Wordpad or a spreadsheet (or loaded into QlikView!).

The exported Expression Overview text file makes an excellent addition to documentation for a complex report for those of you who are required to produce documentation.

Expression Knows Which Cycle Group Field is Active

2008-10-03T16:53:00.004-04:00

Someday you may have a chart like this: there's a cycle group in the chart dimension and all of the expressions work great except that one expression needs to work differently when one particular field of the cycle group is active. Here's an example-- your chart for current month sales is working ok but the expression for allocated sales revenue must work differently when the cycle group field Division is used. Maybe the accounting department has decreed that a complex, ever changing formula must be used to show allocated sales revenue by Division so your report needs simply to show 'Not Defined' in the allocated sales revenue column when Division is used for the dimension.
You can write the expression something like this:
If(GetCurrentField(CorpCycle)='Division','Not Defined',Sum(alloc_sales))

In this example, CorpCycle is the name of the cycle group. Division is the name of the cycle group field. Use the actual field name not the label that might be used in the chart. If you're not familiar with using a cycle group in the chart dimension then it will be worth your time to find the reference manual or tutorial and read about it. Being able to change the chart dimension instantly with a mouse click is a powerful feature.

If you build your expressions using the wizard available on the Edit Expression window, you will find the GetCurrentField function along with several other interesting functions listed under System Functions.
Another common place to use this function is in the chart title. For example, you might use this for a chart title:
='CURRENT MONTH SALES SUMMARY BY ' & GetCurrentField(CorpCycle)
That will use the currently active field name from the cycle group in the chart title. As a user clicks on the cycle group the chart title will automatically change along with the dimension.

Step Through Field Values

2008-09-27T11:52:00.007-04:00

Author's Note: This blog posting is obsolete. There is a much easier way to accomplish this in QlikView without a macro (Click here to read it).

Often, when we're analyzing data we need to examine the tables and graphs on our QlikView report for each value of a field. For example, we might need to check on inventory values and forecast error separately for each warehouse location or maybe for each product category or maybe we need to look at the data separately for each month of the year. Here's something that helps with that process. Add a button to your report and name the button "Step Through Field Values". Then, add this macro to the module and associate it with the button:
SUB StepThroughValues
'--------------------
'Step through all the available values for a field
'selecting each value one at a time
fieldName=trim(inputbox("Enter a field to cycle through. (Case sensitive and spelling counts!)","Step Through Values","Material"))
If fieldName="" then
Exit sub
End if
Set val=ActiveDocument.Fields(fieldName).GetPossibleValues(20000)
'Set val=activedocument.Fields(fieldName).GetSelectedValues(20000)
For i=0 to val.Count-1
ActiveDocument.Fields(fieldName).Select val.Item(i).Text
returnval=msgbox(val.Item(i).Text&chr(13) & i+1 & " of " &val.Count & chr(13 )& chr(13)& "Click:" & chr(13) & " YES: to create Bookmark" & chr(13) & " NO: to move to next entry" & chr(13) & " CANCEL: to quit", 259, "Step Through Values")
if returnval=2 then
Exit For
ElseIf returnval=6 then
bmarkName=inputbox("Enter a Bookmark Name", "User Entry", "BookMark " & i+1 & ", " & fieldName & "=" & val.Item(i).Text)
ActiveDocument.CreateDocBookmark false, bmarkName
End if
Next
Activedocument.Fields(fieldName).Clear
Set val=Nothing
End sub

I know that this blog format and your browser will be wrapping some of the lines in the macro and removing the indentation that I use to indicate code structure so be careful. You should be able to select and copy the code from your browser window and then paste it into the QlikView macro.

When you click the button on your report a window will appear that asks you for the name of the field to step through. Use the actual field name that was loaded into QlikView. This is case sensitive and must be the correct field name spelling. The macro will then select the first available value for the field and offer you the opportunity to create a bookmark for it. Click on No to make the macro move on and select the next possible value for the field. The bookmark is just to help you come back later and do further analysis. The macro could also be coded to write out a chart as a file or save a graph or do some other useful thing.

Note that there's a commented out line in the macro that will make it step through each of the currently selected values for a field. If you prefer that functionality, just uncomment that line and comment out the previous line to make the button step through the selected values instead of all possible values. In either case, by the time you are finished with the button it will have worked with selections for the field so you may have to restore your original selections (or use the Back arrow to move back through selections).

I hope you find the button useful. Please add a comment to this posting if you have any improvements or suggestions.

Loading All of the Files from a Folder

2008-09-22T16:42:00.007-04:00

It's pretty easy to load all of the files from a folder into QlikView. For example, if you have a number of Excel spreadsheets in the f:\Finance\monthly_summaries folder and each spreadsheet contains a tab named EOM_SHEET and the column names on that tab are consistent in each spreadsheet file then these lines in your loadscript will load all of the spreadsheets into your QlikView report:

Directory f:\Finance\monthly_summaries;
For each ExcelFile in filelist ('*.xls')
EOM_HISTORY:
Load * From $(ExcelFile) (biff, embedded labels, table is [EOM_SHEET$]);
Next ExcelFile;

Note that this looping through the files of the folder will only use filenames that end with a .xls extension. So, any other documentation or data files in the folder will be ignored. The spreadsheets can contain other worksheet tabs too -- only the worksheet tab named EOM_SHEET will be loaded into QlikView. Hundreds of spreadsheet files can be loaded fairly quickly this way. As always with QlikView object names spelling counts and it's case-sensitive, e.g. EOM_SHEET is not the same thing as EOM_Sheet. If you're worried that not all of the spreadsheets use the same column headings then it might be best to code each column heading as a specific field name in the Load statement. That will make QlikView call it an error if the column headings in any of the spreadsheets don't match the expected field names. The error is usually better than loading inconsistent data. The Directory statement and Load statement can also make use of variables to make them more flexible to handle folder and file naming conventions. The load statement, of course, can be made to fit most situations with Where clauses and Group by options.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
In a comment, Guido correctly pointed out that this syntax achieves the same result and it's simpler:
EOM_HISTORY:
Load * From f:\Finance\monthly_summaries\*.xls (biff, embedded labels, table is [EOM_SHEET$]);

Check out the other comments to this posting.

Fixing the Field Name in an Exported Bookmark File

2008-09-20T09:20:00.008-04:00

Last month, I wrote about how useful bookmarks are because they can be exported as a file and then imported into different QlikView reports (Click here to read it). A problem you might run into from time to time is trying to use a bookmark file where the field name in the original report is different from the field name in the report where you want to import the bookmark. It's the same data but perhaps the field name in the original report was spelled differently. This kind of thing happens all the time when data is loaded into QlikView from spreadsheets. You can edit the bookmark file to fix that problem.

Here's an example:
Imagine that your co-worker, Kavya, in the Finance department has a QlikView report with all of the sales districts selected that will be used for the new product rollout. You could really use that bookmark for your project. She makes a bookmark of the selection and exports it and emails you the bookmark file, named Rollout_districts.qbm. But, after you import the file and click the bookmark name and nothing is selected, you discover that Kavya named the field in her report sls_district and in your report it is named Sales_Districts. If either Kavya's report or your report could easily be reloaded then you might be able to fix the field name during the reload - OR - you can quickly edit the field name in the bookmark file and make it usable.
Find the file (in this example, Rollout_districts.qbm) in Windows Explorer. Right-click on the file and select Open With->Notepad. The bookmark file is a kind of text data known as XML. It's ok if you never heard of XML; you don't need to know anything about it for this process. In the Notepad window select Edit->Replace... and when the Replace window opens type sls_district in the Find What: field box and type Sales_Districts in the Replace With: box (*See the diagram at the top of this posting - click on it for a better view). Now, click the Replace All button and then click Cancel to close the Replace window. Click File->Save As and save the bookmark file under a different name (just in case you need the original file again). Don't edit or change anything else in the bookmark file. Finally, import the new bookmark file into your report and you'll have the selections just as you wanted.

For this process to work the way you want, of course, it must be the same kind of data in the fields in both the original report and the report being edited. You can't change a bookmark file of country names into a bookmark for movie titles just by editing the field name.

Automatic Concatenation Watch-Out

2008-09-13T11:23:00.007-04:00

This is a mistake I make every once in a while and it always puzzles me for a few minutes before I figure out what happened. In the loadscript, if you load data from several sources and use exactly the same field names then QlikView will automatically concatenate the data from the second source onto the table created for the first source. It does this unless you specify the keyword NOCONCATENATE.

Here's an example: Imagine that your loadscript loads data from a customer master table and following that you write two load statements to create a small table of just the Club customers and a second table of just the Military customers. Maybe you intend to use the small tables in a Where Exists statement further down in the script. You might write the loadscript statements like this:

CLUB_CUSTOMERS:
Load customer_no
Resident CUSTOMER_MASTER
Where customer_type = 'CLUB';
MILITARY_CUSTOMERS:
Load customer_no
Resident CUSTOMER_MASTER
Where customer_type = 'MILITARY';

That's not going to work the way you intended. There's no error or warning but after the loading you'll discover that there is no MILITARY_CUSTOMERS table. What happened is that QlikView concatenated the customer_no values from the second load statement onto the CLUB_CUSTOMERS table. It did that because the field names in the second table are exactly the same as the field names from the first table. It will do this even if the two load statements are not one after the other like in my example but even if there are other loadscript statements in between. The automatic concatenation feature is probably useful in quickly assembled loadscripts but it can cause trouble for more complex applications. You could change the field name in the MILITARY_CUSTOMERS table to be different, like customer_no_military and the automatic concatenation won't happen. OR, and this is my preference, add the NOCONCATENATE keyword to the second load statement like this:

MILITARY_CUSTOMERS:
Noconcatenate Load customer_no
Resident CUSTOMER_MASTER
Where customer_type = 'MILITARY';

Then you will end up with the two small tables that you intended.

Another keyword, CONCATENATE, is sometimes useful when you want to load two sets of data where the fields are not exactly the same into the same table. When you use this one though it will concatenate the data being loaded to the previous table in the loadscript.

Reducing Report Size

2008-09-11T13:33:00.004-04:00

In the previous posting I showed how a table in an existing report could be changed to make the table smaller (Click here to read it). In a comment to that posting, Rob Wunderlich correctly points out that a much simpler way to make the report smaller is to select the data you are interested in retaining and then, from the menu, select File->Reduce Data->Keep Possible Values. Then save the report under a different name in case you ever want to refer to the original. Thanks for the comment, Rob. That technique is great for permanently removing data from a report for any reason, not just to make the report smaller (e.g. removing old data, sensitive, irrelevant, unneeded, or erroneous data).

The method shown in the previous posting for changing a table in an existing report is still useful for changing field values, adding or joining new fields, etc. Someday I'll have to edit that posting to a more relevant example.

Change a Table Without Reloading Report

2008-09-09T16:54:00.006-04:00

Last month I wrote a posting about interesting things you can do in the loadscript with a Partial Reload. Here's another:

I had a large report that we needed as an example for a presentation but it was just too large. The original database it loaded from is no longer available, so I needed a way to make the report smaller without reloading it. It had one large shipment data table in it and I decided to change the the table to only include two shipping location codes which would make it much smaller.

See the picture above (click it for a better view). I inserted this code at the top of the loadscript:

/* Example loadscript fragment to adjust a table */
/* Put this code at the top of the loadscript and */
/* then run a Partial Reload. */
/* Remove this fragment after using it. */

//Rename the table we want to change
RENAME TABLE HIST TO HISTX;

//Rebuild table with a WHERE clause
HIST:
ADD NOCONCATENATE LOAD * RESIDENT HISTX
WHERE (LOC='001099') or (LOC='011098');

// Drop the original table
DROP TABLE HISTX;

//so that we don't fall into the regular loadscript
EXIT SCRIPT;

Then, I saved the report file under a different name (just in case) and ran a Partial Reload. The Partial Reload executed the commands at the top of the loadscript without removing all of the other data and made the report file a lot smaller and the memory or RAM requirement for the report smaller too. Unless you know that you'll never need to reload the report again you should remove the loadscript lines you added at the top.

Don't forget the ADD or the NOCONCATENATE keywords or you'll find that the table you wanted to rebuild is missing after you run the Partial Reload.

Checking for Loadscript Errors from the Macro code

2008-09-06T10:10:00.007-04:00

If you are using the macro code Reload method to load data into your QlikView report you can also easily check for any loading errors. Here's an example of how that could be done. Add a small subroutine to the macro code that can check the system variable for error count like this:

Sub Check_for_errors()
'---------------------
'See if any load script errors occurred and report them
alarm_flag = 0
Set objvar = ActiveDocument.Variables("ScriptErrorCount")
If trim(objvar.GetContent.String) = "0" then
  'No error - hooray
  Exit Sub
  End If
alarm_flag = 1
'Here you can add any other error notification
End Sub

And then, in the subroutine that reloads the document code add an IF statement after the .Reload method something like this:

ActiveDocument.Reload
Check_for_errors 'call Check_for_errors subroutine
If alarm_flag = 1 then 'an error happened in the loadscript
  ActiveDocument.CloseDoc
  ActiveDocument.GetApplication.Quit
  '***Exit Function or Exit Sub
  End If

In the reports I've developed there is error checking every step of the way and any error causes a notification file to be written out. If you have an email client program on the computer where the report is reloading then consider using an email notification to the report users or to a technical support person. I've sometimes sent a small notification email message to my cell phone (My favorite message is "Lassie, go for help!") so that I can be notified immediately with the bad news.

Checking for errors when a report reloads automatically

2008-09-04T18:05:00.009-04:00

In a recent posting (Click here to read it) we discussed ways to run QlikView from the command line as part of an automated script process. If you run the reload process automatically then you're faced with an issue: what should happen if an error occurs during the reload? Even a report that has reloaded successfully dozens of times before can run into an error if a file is missing or the database is down or if someone changed the title on a spreadsheet column (that's the one I seem to trip over most often).
If you're going to sit at your desk in front of the computer and watch it run then you don't have to do anything -- any loadscript error window that opens up can be clicked and the report can be cancelled if necessary. But, if the report is going to load unattended without anyone watching then consider these actions:

Check the Generate Logfile box on the General tab of the Document Properties window (select Settings->Document Properties from the menu - see the picture above). This is often a good idea whether you are reloading the report automatically or not. It causes QlikView to generate a text file audit trail of what happens as the loadscript runs. It captures the number of rows returned from queries and which branch an IF statement takes, it shows the expanded value of document variables that are used in commands and expressions and it shows the date and time for each command which allows you to analyze the slow-running parts of the loadscript. The logfile will be named just like the report file but with a ".log" added to the end. For example, if your report file is named Prior_Yr_Sales.qvw then the log file will be named Prior_Yr_Sales.qvw.log and it will be stored in the same folder as the report file. Open the log file with Windows Notepad or Wordpad. You can search for errors in the log file by using the Notepad or Wordpad Search function and search for Error:

For a report that must reload unattended (with no human folk watching) add this line to the top of your loadscript:
SET ErrorMode=0;
That will tell QlikView not to open a loadscript error window when an error occurs (which would wait for someone to click OK). Instead the loadscript will go through all of the commands and do the best it can to execute all of them.

From your .bat or .cmd script you can have the script search for any lines containing the text Error: and perform an action if it is found. Here's an example:
rem Initialize the log file with xxx
echo xxx > c:\RPTS\Inventory.qvw.log
rem Now, run QlikView and reload
"c:\Program\QV.exe" /r /vbatch_flag=1 c:\RPTS\Inventory.qvw
rem Look through the log file and write
rem a note to Joe if we find an error
Type c:\RPTS\Inventory.qvw.log Find /c "Error:"
If errorlevel 1 echo %date% %time% "Error occurred" >> c:\Notify_Joe.txt

QlikView Command Line and Automation

2008-08-31T17:30:00.009-04:00

This posting is for the more technically inclined among you and developers who work with reports that must reload automatically.

You can run QlikView from the command line. You've probably created shortcuts on your desktop to various useful QV reports. In that kind of shortcut the command is simply the complete pathname to the report file. That will also work in a .bat or .cmd file; just enter a usable pathname to the report file and Windows will open QlikView for the report.
More interesting things can be done with a command that includes the pathname to the QlikView program followed by a pathname to the report file. This command line syntax allows you to enter switches or parameters. The command line syntax is covered pretty well in the reference manual. For example: If the pathname to your QlikView program is c:\Program Files\QlikView\QV.exe and the report you want to open is c:\reports\Inventory.qvw then this line will do a simple open of the report in a shortcut or .bat or .cmd file:
"c:\Program Files\QlikView\QV.exe" c:\reports\Inventory.qvw

You may or may not need the quotes around the pathname for QV.exe or for your report file. Windows usually wants to have the quotes for any pathname containing a blank or special character.

If you add a /r switch (notice the front-leaning slash) then the command will open the report, run the reload function (executing the loadscript), and then save and close the report:
"c:\Program Files\QlikView\QV.exe" /r c:\reports\Inventory.qvw

A /rp will do something similar except it runs the partial reload function.

The /l switch (that's a lower case ell) will open the report, run the reload function and leave the report open. This one can be useful as a desktop shortcut:
"c:\Program Files\QlikView\QV.exe" /r c:\reports\Inventory.qvw

A /p switch will open the report, run a partial reload and then leave the report open.

The /v switch can be used to pass a document variable value into the report. It can be used either with or without the switches described above. The document variable might be used in macro module code or in the loadscript. In our example if we want to reload the QV report and pass in the document variable batch_flag with a value of 1 then we'd use a command like this:
"c:\Program Files\QlikView\QV.exe" /r /vbatch_flag=1 c:\reports\Inventory.qvw

[The previous line and all of the previous command line examples are intended to be a single line even if your browser is breaking them into two lines]

We often use a command like that for reports that work differently when they are executed in batch versus being opened by a user.

My personal favorite way to use the command line syntax is from a VBScript file (.vbs script) on the Windows PC. VBScript allows me more flexibility in automatically figuring out which report file to run and what kind of document variable values should be passed in. From the VBScript file the syntax might look something like this: (using the same example)
Set objShell = CreateObject("WScript.Shell")
objShell.Run """c:\Program Files\QlikView\QV.exe"" /r /vbatch_flag=1 c:\reports\Inventory.qvw"

[Set objshell... is a single line and objShell.Run... is the second line no matter how your browser is breaking them up]

In addition to filenames using drive letters you can use network file names like this: \\canserver\projects\reports\Inventory.qvw

VBScript in Loadscript and Module

2008-08-28T21:50:00.008-04:00

The language used in both the QlikView loadscript and in the module code (macros) is modified VBScript. You can easily find information about VBScript commands and syntax through Google. For example, to find information about msgbox prompts and syntax enter this in Google:
vbscript tutorial msgbox

The best thing about Bookmarks

2008-08-26T14:28:00.000-04:00

Many QlikView users don't make much use of the Bookmark feature. The Bookmark feature lets you save your current selections and give it a name so that you can easily recreate those selections later.

I have found the most useful part of bookmarks is that they can be exported as a little file. Then the little file can be imported back in to the report tomorrow or it can be imported into a different report. We have quite a few reports that include the field name, ITEM, which is a product identifier. Quite often, I have a selection of several ITEM values from one report that I'd like to see as a selection in a different report. I can just make a bookmark, export the bookmark, and then import the bookmark file into a different report and have those same ITEM values selected. If there are any field selections from the first report that don't make sense in the second report because the fields so not exist they are just ignored.

Sometimes a co-worker will send me a spreadsheet of data that requires further analysis. I can quickly import that spreadsheet into a new temporary QlikView report, select the ITEM values to be investigated, make a bookmark and export it. Now, I can import that bookmark file into one of our comprehensive QlikView reports and review data for the ITEM values from the spreadsheet.

Copying a Chart From One Report to Another

2008-08-24T11:55:00.001-04:00

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.

QlikView Reference Manual

2008-08-24T08:54:00.001-04:00

A co-worker, one of the newer guys, asked me a question about QlikView the other day. He had an old copy of the QV reference manual displayed on his laptop. “Where did you get that?”, I asked. “That’s the manual they gave me when my laptop was set up”, he replied. As the Greek poet Homer said, “D’oh!”. I can’t use the general-purpose consultant answer RTFM if the user’s FM is two versions old!

You may already have a current reference manual on your PC. If you installed the full version of QlikView then the reference manual corresponding to your version of QlikView can be found in the same folder as the QlikView software programs (typically, c:\program files\qlikview). If the manual isn’t there, consider re-installing QlikView and this time specify the full installation. If you are in a corporate environment and your I.T. group can’t or won’t provide a QV reference manual then you can find one on the QlikView website, qliktech.com; you’ll have to register for QlikCommunity in order to access the reference materials. A visit to the website is worthwhile whether you need a new reference manual or not – I’m sure you’ll find something interesting.

Also check out this more recent posting about QlikView help: (QlikView Tutorial)

The reference manual can’t be read like a novel but search for a topic whenever you feel you’d like to know more and you’ll become more proficient with data analysis. If (like most) you are not a manual reader then you can generally find answers to your questions through the Help text available throughout the QlikView application.

Field names and compressing data

2008-08-23T09:30:00.001-04:00

One of the cool things about QlikView is the way it can store enormous amounts of data in the report file. It can load many millions of rows of data into a report file that might only be one or two MB in size. It compresses data so well that I seldom zip a report file before sharing it since the data is stored so densely in the QV report that zipping the file doesn't give much of a savings. If you are working with loading very large tables into a report be aware that part of the QV strategy for compressing data depends on not actually storing duplicate values in a field. If the report loads sales order data, for example, and the data contains customer name then there may be many duplicate values in the data for any customer who has ordered many times. QV will only store the customer name once and then keep track of where that customer name is used again as subsequent rows are loaded.

This mechanism means that if you load two different tables from your database that contain customer name into two different QV tables and if the field name is the same for both QV tables then each customer name value is only stored once. But, if you give the field a different name in each QV table then the program cannot know that it is the same data and it must treat each field separately and many customer name values will be stored twice. This is really only a consideration when working with enormous tables where the sheer size of the tables is affecting memory utilization of the report or the size of the report file.

Remember that QV relates or joins the tables together based on the fields they have in common. Two QV tables that both contain the field Customer_Name will be related based on that field. So, sometimes making the field names in the tables the same to be more efficient with memory utilization will not be possible if it causes two tables to be joined when they shouldn’t (the joining of tables also consumes memory space). And the field names should only be the same if the field data is truly the same. Customer number from your company’s database is not the same as customer number from another company’s database. But, customer number from your company’s 2006 sales data is probably the same as customer number from the 2007 sales data.

Changing Daily Data to Weekly in a Chart

2008-08-21T18:10:00.000-04:00

I got a call earlier today from an analyst who had a QlikView report loaded with detailed shipment data. The main chart on the report showed shipment totals by day but the analyst needed the chart to show weekly quantities -- "is there a way to make it show the weekly data?"

"Well, you're in luck. Not only can it be done but you can do it yourself in a few minutes." Follow these steps:

Open the QlikView report and save a copy of your report under a different name by selecting File->Save As from the menu (just in case I give you bogus instructions that don't work... it happens).

Right-click on the chart that shows the daily data. Select Properties and select the Dimensions tab. It might look like the diagram above. [Click on that diagram to make it bigger and get a better view]

You'll see a SHIPDATE entry in the Used Dimensions window. Click on it and then click the Remove button

Now, click on Add Calculated Dimension... the Edit Expression window opens up. We're going to add an expression that yields a weekly date to replace SHIPDATE. Type in this expression:
WeekStart(SHIPDATE)

Click OK to close the Edit Expression window

Give our new dimension a name- type WEEK in the field name box. See how it might look in the diagram below.

Click OK to close the Chart Properties window

Now the chart shows data by WEEK instead of SHIPDATE. This same method can be used for other types of date conversion functions or most other kinds of numeric or text string calculations. It lets you use a chart dimension even though the dimension data doesn't actually exist that way in the loaded data.

More you can do with Partial Reload

2008-08-20T17:43:00.000-04:00

See the previous posting about removing tables. In the same way that you can enter a Drop Table command to remove a QlikView table from an existing report, you can enter these commands which will affect the existing report already loaded with data:

To remove a field from every QlikView table in which it appears, enter Drop Column;
For example, for privacy reasons you might want to get rid of social security number data in your report before sharing it with your team. You might enter:
Drop Field Customer_SSN;

To remove a field from specific table(s), for example, you might enter:
Drop Fields Customer_Name, Customer_SSN from CUST_MSTR_TABLE, ORDER_HDR_TBL;

To change the name of a column or field in every table in which it appears enter Rename Field; (remember that the field names are how QlikView relates or "joins" the tables together so this one affects that table relationship).
For example, you might enter:
Rename Field domestic_custno to North_America_Customer;

You can change the name of an existing table. For example, you might enter:
Rename table domestic_sales to North_America_Sales;

All of these commands can also be used in the loadscript during a normal load but they can be especially useful for fixing issues when used with an existing report that is already loaded with data. Remember to put them at the top of the loadscript, add an Exit Script; command and then execute the commands with the Partial Reload. The rename commands can be used with a mapping table if you think you might need more powerful renaming capabilities. Refer to the Help Text or Reference Manual for info about how to use a mapping table for renaming fields and tables.

How to remove an existing table

2008-08-20T13:54:00.001-04:00

For this first blog entry we'll talk about something simple although maybe not a beginner's task. How do you remove a QlikView table from an existing report that is already loaded with data? There's a variety of reasons you might do something like that: to remove a large table and reduce the size of the report, maybe the data in one table is incorrect or causes incorrect results in the charts, or maybe you intend to share the report with Marcy in Finance and she just doesn't need to see the sales forecast. Whatever the reason, it is fairly easy to remove a table.

First, make a copy of the report you are going to change so that you can restore the report in case something goes wrong or you make a mistake (it happens). Open the report, then select File->Edit Script from the menu. It will open the loadscript edit window. Insert a few blank lines at the very top. Now type in a drop table command for the table you want to remove. For example, if you want to remove the NEW_ORDERS table then type in
DROP TABLE NEW_ORDERS;
EXIT SCRIPT;

Don't forget the semi-colons at the end of those lines. It might look something like the picture up above.

After you type the new lines in the loadscript, review the lines to make sure everything is spelled correctly and click on the OK button to close the edit loadscript window and then select File->Partial Reload from the menu. The partial reload choice will execute your new loadscript lines down to the Exit script line and then stop. The NEW_ORDERS table has now been removed from the report. If you ever expect to reload the report again you should edit the loadscript again and remove the new lines you just inserted. Then click OK to close the edit loadscript window and Save the report (or do a Save As to save the report under a different file name).

QlikView Maven

Count of Rows in a Chart

Select Top 4 Values On Open

Replace IF in Expression with Set Analysis

Increasing Likelyhood of Duplicate Values While Loading Data

Using Oracle Configured for Different Countries

Looking Inside a Bookmark Without Opening It

A Chart Expression That Works Differently for Totals Row

Duplicate Rows in a QlikView Table

Loading Multiple Files With Varying Fields

Export All Data From All QV Tables to Text Files

Variable That Acts Like a User-Defined Function

Loading Scientific or E-Notation

Time interval between two timestamps

Easy Text Search

Selecting Null Values

Random Sample Selection

Bookmarks for Dynamic Selection

Listbox or Multibox for Group of Things That Satisfy a Condition

Calculated Dimensions to Show Things Satisfying a Condition

Export Data From QlikView Table

Select Top Ten ITEM Values

Stepping a Selection Through Each Value of a Field

Fire a Trigger When User Changes an Input Field

Activate Multiple Listboxes

Restoring the selection for a single field from a bookmark

User Option done as a Field Selection

Combining Bookmarks

The Case of the Disappearing Object

Load from Excel even if you don't know the tab name

Test if QlikView table exists within the loadscript

Set analysis and chart dimension

Quick load of max field value

Browse-for-File Macro Button

Finding Missing Dates With a Chart

Using QlikView to Look at a Big Text File

Report Tells User ABOUT The Data

Highlighting a Chart Value

Capture Variable Values in the Loadscript

Version 7.50 Chart Memory Errors

QlikView Exists Function

Benefits of Concatenating a Code and Description

Error: fetched column value was truncated

Load a Table With All of the Values for a Field

Archive Data

Adding an Image to the Document

See a Title Tab When Report Opens

Loading Variables With A Macro

QlikView Tutorial

Variables in the .bat script

Loading Variables Via Loadscript

Listing All Variables and Contents

Copying Variables From One QlikView Report to Another

Putting a Count in the Chart Title

Set Analysis

Variable Overview

Expression Overview

Expression Knows Which Cycle Group Field is Active

Step Through Field Values

Loading All of the Files from a Folder

Fixing the Field Name in an Exported Bookmark File

Automatic Concatenation Watch-Out

Reducing Report Size

Change a Table Without Reloading Report

Checking for Loadscript Errors from the Macro code

Checking for errors when a report reloads automatically

Other QlikView Tutorial Blogs

QlikView Command Line and Automation

VBScript in Loadscript and Module

The best thing about Bookmarks

Copying a Chart From One Report to Another

QlikView Reference Manual

Field names and compressing data

Changing Daily Data to Weekly in a Chart

More you can do with Partial Reload

How to remove an existing table