Looking Inside a Bookmark Without Opening It

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.

You can also apply the selection to the ITEM field without opening the Feb_Sales bookmark. Go to a listbox or multibox entry for ITEM and type this:

=count({Feb_Sales} 1 )

and that will select the ITEM values (if none of the ITEM values exists in your current document then it will appear that all values have been selected).

If there is a field normally associated with ITEM then you can select that. For example, if the field Product_Category is normally associated with ITEM then go to a listbox or multibox entry for Product_Category and type that same bit of text and it will automatically select the Product_Category values associated with the Feb_Sales bookmark without opening the bookmark!

* * *

Activate Multiple Listboxes

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

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.

★ ★ ★

Combining Bookmarks

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 )

★ ★ ★

Set Analysis

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.

Fixing the Field Name in an Exported Bookmark File

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.

The best thing about Bookmarks

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.