Showing Values From a Parameter File With Set Analysis

I helped a coworker recently with a QlikView document that needed to show data from a parameter file. This parameter file was organized like many files that store settings or configuration or set-up values or .ini data – there are two columns: a parameter name column and a parameter value column. It is a popular format for designers because the parameters can be easily changed and new parameters added just by adding a new row.

My coworker’s document has a chart expression that should show the paint color ordered with an automobile kit if a specific paint color is in the order, otherwise, if the Paint_Color field from the order is null, then a default paint color should be shown that comes from a parameter file.
The parameter files two columns are: param_name and param_value. The parameter that holds the default paint color for the automobile kit is named “DEFAULT_PAINT”; that's the value of the param_name column and the param_value column on the same row holds the actual default paint color.

We wanted a solution that didn’t require changing the document loadscript; something that could be done in the expression. We decided to use some Set Analysis syntax because the normal QlikView associative connections wouldn’t easily let us show the param_value for the default paint color. The Set Analysis syntax would be used within a function to simulate the selection of the param_name field value “DEFAULT_PAINT”. Here is the expression we used:

If(IsNull(Paint_Color), Only( {$<param_name={'DEFAULT_PAINT'}>} param_value), Paint_Color)

We used the Only function because it allows Set Analysis syntax within its arguments but the If statement, by itself, does not.

I know Set Analysis syntax is complicated but the basic idea combines the concept of sets that you learned in high school math class with the QlikView concept of selection which you are already familiar with from using QlikView. It is a powerful technique worth taking the time to understand.

QlikView Ninjas reading this blog posting may see a resemblance between the parameter file I described and a mapping table in a loadscript. Indeed, if you keep the table around after the loadscript finishes (mapping tables are automatically deleted at the end of the loadscript), then the technique in this posting may be used to achieve something like mapping a value from a mapping table -- but doing it in a chart expression!

* * *

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.

★ ★ ★

Change a Table Without Reloading Report

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.

More you can do with Partial Reload

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

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).