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'

* * *

Using a Cycle Group in a Text Object

People usually use Cycle Groups in the definition of dimension for a chart. You can use cycle groups in other places if it helps with understanding your data. Here’s an example showing how you could use a cycle group with a text object to list out top performers at various levels in terms of sales--

First, define a cycle group with aggregation levels that make sense for aggregating sales. For this example I created a cycle group, named xgroup, and defined it as salesperson, sales_region, sales_district, product, and customer_group. The text object won’t provide a place to click on the cycle group and change the level, so I defined a small straight table chart using the cycle group, xgroup, as the dimension and one expression defined simply as 1. Then, I defined the number of rows to show zero rows so that only the totals row is visible and define the totals as sum-of-rows so that the number in the total is the number of distinct values from your cycle group.

You can use any expression that would be of value, but for this example we want to list the top five names in terms of total sales. If the cycle group was set to salesperson then it would list the top five salespersons in terms of total sales. Here’s the text expression I typed into the text object:

='Top 5 ' & GetCurrentField(xgroup)
& ' by Total Sales:
' & concat(
aggr( if(rank(sum(Sales))<=5,
$(=GetCurrentField(xgroup)) ), xgroup)
,', ' & chr(13))

The first line in the expression defines the first line that will appear in the text object and it is a title for the data. It uses the GetCurrentField function to get the current value of the cycle group and use it in the title.

The main part of the expression is an aggr function that will return an array of five values from aggregating the simple if statement over the values as defined by xgroup. I used a concat function to put a delimiter between the values of the array so it will show up in the text object. The delimiter will be comma followed by a space and then the chr(13) function is a carriage return or newline value to put each value on a new line. So, our finished text object might look something like this in the document:

Top 5 salesperson by Total Sales:
S.Hallas,
T.Mehta,
Z.Sui,
P.Esposito,
M.Naverra

Every time you click the cycle group icon on the little straight table chart, the text object changes to show results from a different level: sales_region, sales_district, product, or customer_group. If you place several of these text objects side by side then each could have a different expression. The expressions might be things like top 5 by count of sales orders, or top 5 in profitability, or total promotion budget, or total installation costs, or count of returned products, etc. The idea is to provide some extra information in the document using the flexibility of a cycle group and the simplicity of a text object.

The text objects are most useful and interesting, I think, when several of these expressions are used together in the same object along with text that explains, identifies, and provides meaning.

* * *

Cool Idea for Simplifying Chart Expressions

I have an inventory reporting document that shows inventory quantities at the end of each month. The basic unit-of measure for inventory is “cases” but the material master table contains conversion factors for each product to convert quantities in cases into other units of measure like kilograms, pounds, pallets, or inventory cost in dollars.

Similar to what I’ve done in the past to help a user select date formats, or currency, or language; I set up a small inline table in the document loadscript that looks like this:

UOM_SELECTION:
Load * inline [
UOM_SELECTION, Abbrev, ConvFactor
Cases, CS, 1
Kilograms, KG, CONV1
Pounds, LB, CONV2
Pallets, PT, CONV3
US Dollars, USD, CONV4
];

In the document, the field UOM_SELECTION appears in a multibox and the user selects the unit of measure they want to see used in charts and calculations. The field is configured for “Always One Selected Value”. When the user has made a selection then the Abbrev or UOM_SELECTION fields can be used in titles or labels since there is only one possible value for those fields.

The ConvFactor field is interesting – it is not really a conversion factor in the little table, it is the field name of a field that appears in the material master. For example, each material in the material master has a CONV1 field whose value is the conversion factor to convert cases of that material to kilograms. In the chart expressions where the inventory quantity must be shown, the unit of measure is handled, not by a series of IF statements, but with the name of the conversion factor field. A typical expression would look like this:
Sum( cs_quantity * $(=ConvFactor) )

The ConvFactor is evaluated and the value, which is a field name, is used in the expression. In the inline table, the value of ConvFactor for cases is simply a 1 since no real conversion is necessary and multiplying by 1 works fine. You are not limited to a single field name in the inline table-- you could use something like CONV5/CONV6 or 1/CONV5 and it would be evaluated in the chart expression just like a single field name. The chart expression is faster and simpler without the IF statements that would otherwise be required to determine which unit-of-measure was selected.

* * *

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!

* * *

Expression Overview - Find/Replace

The desktop QlikView Expression Overview is a useful tool for searching expressions in a document or doing a Find/Replace on terms and values in the expressions (from the desktop menu Settings->Expression Overview). Many people put expressions or parts of expressions into document variables mainly so that they have only one thing to change when the expression needs to change instead of changing individual expressions in multiple places in a document. With the Find/Replace functionality you can accomplish the same thing: make the change in only one place and effect the changes wherever needed throughout the document.

If you use text expressions for column labels and object titles then the Find/Replace function can change those too. Find/Replace will also change text in the expression comments; something I find useful since comments often refer to field names and function names. Some people put a little label or tag in a comment line in the expression to help them find those specific expressions later. For example, you might put a comment in the expression that looks like this, /* Tushar, please review this */ to help a quality review person find the expressions that need to be reviewed. (To make a comment in the expression either put /* before the comment and */ after the comment, or, use // to indicate that the remainder of the line is a comment.)

One thing to remember is that the Find or Find/Replace will not find hidden objects. When I am using Find/Replace functionality on a document with hidden objects I usually change the document properties Security tab and click on Show All Sheets and Objects. Then, Find/Replace can make a change to all expressions, even those on the hidden objects. After making the change, update the Security tab again to uncheck the Show All Sheets and Objects box.

If you want Find/Replace to avoid changing expressions in a particular object then you can temporarily hide the object while making the Find/Replace changes. To temporarily hide an object go the object properties and type 0 into the Show Condition (type a zero character). After the Find/Replace changes are finished, go to Document Properties, Security tab and click the Show All Sheets and Objects check box. Then, find your object and clear the Show Condition box in object properties. Go back to the Security tab and uncheck Show All Sheets and Objects and your temporarily hidden objects are visible again.

* * *