Export All Data From All QV Tables to Text Files

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

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

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

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

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

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

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)

★ ★ ★