Entire Load Statement in a Variable

QlikView document variables contain text. The software allows us to use variables in different situations and, for such a simple idea, they add considerable flexibility to help you satisfy requirements.
 
Most people use variables for field names, label text, expressions, and other relatively short bits of text. You can store longer text in a variable that may be useful. For example, I recently stored this entire bit of loadscript code in a variable named var_UOM_table:

/* Unit of Measure - loadscript code */
if NoOfRows('TBL_UOM_SELECT')>0 then
    Drop table TBL_UOM_SELECT;
end if
TBL_UOM_SELECT:
Add Load * inline
[Unit of Measure: uom_conv
Box: 1
Case: 0.1
Std Drum: (1/DRUMFCTR)
] (delimiter is ':');


In the loadscript I simply wrote the single line
$(var_UOM_table)

and that expanded to the entire segment of loadscript code when the document was reloaded.

This was useful to me for a document that loaded variable definitions from a database. Remember that variables can contain text expressions and accept arguments. Keep variables like this in mind for loadscript situations that require standard bits of text that may need to be repeated or shared between documents.

★ ★ ★

Always One Selected Value Really

I developed a document a few months ago that provided the user the ability to select which currency (Dollars, Euros, Rupees, etc) to use for presenting financial data. The expressions in the document did not work correctly unless one and only one currency was selected. Originally, I clicked on the Always One Selected Value option available in Listbox and Multibox properties. But there was a problem with the option sometimes turning itself off and the first indication of a problem is when users noticed glaringly incorrect financial quantities!

The problem with the Always One Selected Value option is that it can turn itself off when someone does a Reduce Data operation on the document. My team frequently does that to reduce document file size in order to email a document or store it in the version control system.

Here's an alternative way to accomplish the same thing. In my document, I wanted a single value from the CURRENCY field always selected. So, I set up an action to be executed based on the OnAnySelect or OnOpen events. Go to Settings-Document Properties and click on the Triggers tab. In the Document Event Triggers window click on OnAnySelect. In the Field Event Triggers window, scroll down to CURRENCY (use your field name, of course), click on it and then click the OnSelect Edit Action(s) button.

When the Actions dialog box opens, click the Add button, add a Select In Field action, type CURRENCY (use your field name) into the Field box, and then type this into Search String:

=if(GetSelectedCount(CURRENCY)=1,CURRENCY,'Euro')

That expression makes sure only one value is selected and, if it isn't, it automatically chooses a default value - in this case it chooses Euro as a default currency value. You could also use a variable or an expression for the default value.


Click OK and then do the same thing for the OnOpen event. If needed for your situation you may also want to do the same thing for the OnPostReload event. Now, the document will always have one selected value for the field and if anyone tries to deselect it or select multiple values the trigger will switch to the default selection. For my document, I also checked off the Always One Selected Value checkbox in the Listbox Presentation tab.

[Author's Note 1Mar2013
Based on a reader comment, here's a slightly different expression that will return the selection to the previous valid selection instead of a default value:

=if(GetSelectedCount(CURRENCY)=1,UOM_SELECTION,if(isnull(Only({$1}CURRENCY)),'Euro',Only({$1}CURRENCY)))

It uses set analysis syntax to get the previous selection and only uses the default if the previous selection is null. 

Execute Oracle Procedure From The Loadscript

I worked on a project a few months ago where several Oracle database tables had been designed specifically to support a QlikView document. The tables contained summarized data extracted from a large database. Often, database software, running on a large server can process large amounts of data more efficiently than QlikView can do it within the loadscript. In this particular case, the solution would only work correctly if the tables were updated shortly before loading the QlikView document. I decided to try something different and execute an Oracle stored procedure from a loadscript command. The procedure would update the tables and then the remainder of the loadscript could load data from the tables with SQL select statements.

The syntax for the procedure execution took a little trial and error. I put the execution immediately after the ODBC connect statement. This procedure takes a parameter which is supplied from a document variable. The procedure, named update_rpt_data, is implemented within an Oracle package named corp_report. The loadscript statement looks like this:

// Call Oracle data summarization procedure
sql call corp_report.update_rpt_data('$(var_COUNTRY)');

In order to make this work correctly with Oracle, I had to add a COMMIT statement as the first executable line in the stored procedure. Without the commit, Oracle threw an error.

If you use this technique, remember that the procedure execution may take extra seconds or minutes and this will add to the time required for the loadscript. Other types of database software may require different syntax; this example applies only to Oracle. Note that the statement only executes the procedure – no data is being returned directly from the procedure.

★ ★ ★

Blinking Text Box

Occasionally, I’m asked if QlikView supports blinking text. Here’s a method that you can use if it is necessary. I don’t recommend this technique unless you have some kind of seldom used message that the document user absolutely must see (like “This data is invalid” or “Warning. Your chair is on fire!”) In any case, it is easy to try it out and see what the effect looks like.

Text in a text expression can be made to blink between two alternate forms with something like this:

=if(Odd(Second(Now(1))), '*** Hey, Look ***', '/// *** \\\')

Something more effective at attracting attention is to blink the background color of a text box.  Your text box should show constant text or a text expression that does not use the Now() function. And, the background color expression could be made to blink between two colors like this:

=if(Odd(Second(Now(1))),Cyan(),Yellow())

Expressions like these could be used to “blink” any of the document properties that can be controlled with an expression. The Now function in these expressions does use up some of your computer’s CPU time. A single expression is negligible but if you had several of these expressions using the Now function it could consume more than a few percent of your CPU capacity.


[The fish x-ray pictures in this blog posting and the one last week make great iPad wallpaper images]

  .  .  .

List Box to Select Values From Two Fields at Once

I posted this answer on QlikCommunity yesterday and several people remarked on it so I thought I’d repeat it here.

I recently had a QlikView document that showed lagged sales forecast data. There were two date fields used in the data: a Create_Date indicating when the sales forecast was created, and, a Fcst_Date indicating which week was being forecasted. For example, a forecast of sales for the week of June 21st might be created two weeks earlier on June 7th. Then the Create_Date would be 07JUN2012 and the Fcst_Date would be 21JUN2012.

In the document, in addition to other list boxes and a multibox, I added a list box that allows a user to view and select values from both date fields at the same time.
I created a new List Box and in the Properties General tab, I didn’t select a field, instead I chose <expression> and typed this into the expression:
='Create date=' & Create_Date & ' and ' & 'forecasted week=' & Fcst_Date
I made the title of the list box “Select Create and Forecasted Dates”.
Now, I had a list box that showed the pairs of dates that occur in the data. The values in the list box can be green when they are selected or white when they are possible values and gray when they are excluded values just like any other list box. When I click on a value in the list box it selects the corresponding values from each of the two date fields at the same time.

One additional refinement was to make the values sort the way I want them. I opened the Properties of the list box and chose the Sort tab and checked the Expression box and then in the expression box I typed just the field name, Create_Date

This idea would work with any pair (or trio) of fields where being able to view the field values together and select the values together makes sense.


  *  *  *

Translate a Field Name into a User-Friendly Field Label

In my last two blog postings and in the one I’m preparing for next week I’ve described techniques that use the GetCurrentField  function. Sometimes, a problem with that function is that it returns a field name which may not be recognizable to the people using a QlikView document. Here’s a technique that can translate the field name into a more user-friendly field label.

First, define a table in the loadscript that lists out the field names you will be using in your cycle groups and the corresponding field labels. Here’s a small example using some SAP field names:

FLD_LABEL_MAP:
Load * Inline [fldname, fldlabel, fldshort
MATNR, Material, Matl
WERKS, Warehouse, Whse
KUNNR, Customer, Cust];

Now, imagine that you have a chart showing sales totals with a cycle group named SLSCYCLE in the chart dimension that allows the user to click through three values in the cycle group: MATNR, WERKS, or KUNNR. You would like to refer to the current cycle group choice in your chart title but your document users don’t normally use actual field names when they discuss the data. You can use your preferred field label in the chart title with a text expression like this:

='Sales Totals by ' & Only({<fldname={$(=GetCurrentField(SLSCYCLE))}>} fldlabel )

In that expression, the Only function is included as a way to use Set Analysis syntax to retrieve the user-friendly  fldlabel value corresponding to the field name.
The expression could have used the alternate short label fldshort in a place where shorter text is required. The little FLD_LABEL_MAP table could also contain other data elements related to the field such as the name in a different language or a short piece of help text.

 *  *  *

Using Cycle Group in a List Box Expression

Here are a few more ideas from the Directed Creativity exercise in finding other ways to use Cycle Groups.

You can use expressions in a list box such that only field values satisfying a condition are shown. I’ve written about that before and it has been quite useful (See QlikView Maven from 1Sept2010). Today, we’re looking at expressions that contain a cycle group. This will allow a list box to initially show a list of products with high sales, for example. Then, after clicking on the cycle group icon, the same list box will be showing a list of customers with high sales. The report user need only select values from the list box or right-click and select-all to select the things satisfying the special conditions.

As in last week’s blog posting, I created a cycle group named xgroup containing field names related to sales: product name, sales person, price group, customer, and shipping facility. A typical expression I might use in a list box is to show the top 15 products by sales quantity. Using the cycle group I can define the list box with an expression that easily clicks through the top 15 products, or top 15 sales persons, or top 15 price groups, or top 15 customers, or top 15 shipping facilities.

The values shown in the list box are affected by selection, so, for example, if I start by selecting beverage sales only, then my list box values for the top 15 things is limited to the top 15 things within beverage sales.

Here’s what that kind of expression looks like:

=aggr( if(rank(sum(SALES_QUANTITY),4)<=15, $(=GetCurrentField(xgroup)) ), xgroup)

The title for the list box needs to use the current cycle group choice to make it clear what kind of things are shown in the list box. For my example, the title is a text expression that looks like this:

='Top 15 '&GetCurrentField(xgroup)&'s Based on Sales Quantity'

I can make a series of list boxes like that, each with an expression that shows me something important about the sales information in my document. Or, better yet, use the various expressions as choices in a Multi Box which takes up less room on the screen (although, the list boxes have the advantage of showing the values all the time—as you click through the cycle group you can see the list box values all change as the various cycle group fields are chosen). With simple changes to each of the expressions, my multi box might cover:

• Top 15 things by budget accuracy



• Top 15 things by product failure measurement



• Top 15 things by weekly shipment variability



• Top 15 things by admin and sales cost



• Top 15 things by customer feedback



• Things where sales exceeded 190% of budget

And so on – just think of the questions you have asked of your data in the past.

You cannot change cycle group choices through a list box or multibox. I have at times included a small chart with the cycle group as the dimension and a single expression just to give the report user a place to click on the cycle group icon. The expression consists of a single character: 1. The expression column totals are set to sum-of-rows and the chart is configured to limit the number of rows so that only the totals row is showing. The total for the one expression then is a count of the cycle group things. When the cycle group choice is customer then the expression total is a count of distinct customers. I use cycle group in the expression title too:

=GetCurrentField(xgroup)&' Count'

* * *