Export Data From QlikView Table

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.

★ ★ ★

Select Top Ten ITEM Values

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

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.

★ ★ ★

Fire a Trigger When User Changes an Input Field

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]

★ ★ ★