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.

* * *