Utility That Can Extract and Show All of the Variables from Your QlikView Document

Some of our project teams at work are required to use a standard QlikView document for several clients and then use a client-specific loadscript .qvs file to configure custom variables for each client. The loadscript files define the variables with a set of Let statements and I often get questions about the syntax for the Let statements. Some of the variables define complex expressions and they include dollar signs and functions and quotes and some definitions use multiple lines so the Let statement syntax can get tricky.

I recommend that people do their development first so that they have a working document with all of the desired features and variables that are required. Then, they can use a QlikView document I wrote to be used as a utility program to help people with managing their variables.

You can download a working copy of the utility program, named List_Out_Variables.qvw, by clicking HERE

The utility program has a tab with some text instructions and an input box where people type in the complete pathname to the custom .qvw document that they’ve been working on. Then, they do a reload of the utility program and it extracts all of the variables from the custom document.

The input box stores the pathname in a variable named vDocumentPath. Part of the loadscript in the utility program looks like this:

VARIABLES:
LOAD
  [Name] as VARIABLE_NAME,
  [RawValue] as VARIABLE_VALUE
FROM  $(vDocumentPath) (XmlSimple, Table is [DocumentSummary/VariableDescription]);

That loadscript code extracts the variables from the document and makes the variable names and values available to be shown in charts. (I borrowed the loadscript code from a Rob Wunderlich comment I found in QlikCommunity.)

The second tab on the utility is pretty simple, it is table box showing the VARIABLE_NAME and VARIABLE_VALUE fields along with list boxes for those two fields so that people can select the variables they are interested in. The utility is pretty useful right there as a way to analyze variables, sort them, print them, copy and paste to other documents, etc.

There is a third tab with a chart that helps people with the Let statement syntax that I mentioned. The chart on that tab uses VARIABLE_NAME as the dimension but I found it helps to have the dimension column hidden. Then, there is one expression that converts the VARIABLE_VALUE into a Let statement. The expression looks like this:

'Let '&VARIABLE_NAME&'='&chr(39) &
Replace(Replace(Replace(VARIABLE_VALUE,vQUOTE,vQR),vDOLLAR,vDR),vCR,vCRR)
& chr(39)&';'

An example of how that looks in the actual chart is:

Let MoneyFormat=''&chr(36)&'#,##0.00;('&chr(36)&'#,##0.00)';

To make that work I had to define a few variables in the utility program. It made that text expression much simpler to write. The variables in the utility program are:

vDollar is a variable containing a single dollar sign

vQuote is a variable containing a single quote

vCR is a variable containing a carriage return (really simple, when defining this one just put your cursor in the variable definition window and click your Enter key).

vQR is  '&chr(39)&'

vDR is  '&chr(36)&'

vCRR is  '&chr(13)&'      

If you download a copy of the utility program then you don't have to worry about any of that coding as it is already done for you.

After loading a set of variables extracted from your special document, my instructions tell people to make their selections and then, from the chart, they can right-click and export the chart or copy the data to the Windows clipboard so it can be pasted into a .qvs file or pasted into a loadscript or pasted in some other document.

.

Having an easy method to define a set of variables as Let statements can be used to copy variables from document to document or to accompany a version control process where variables and their values need to be stored as text in a way that allows them to be analyzed or restored later.

Some folks familiar with this blog may remember that several years ago I wrote about an Excel spreadsheet containing a macro that could extract QlikView variable names and values. It worked great but is no longer useful in our corporate environment as the corporate anti-malware software considers it a “suspicious program” and refuses to let it run. The List_Out_Variables.qvw utility is a good replacement.

  ★★★

MonthsBetween Function

A few days ago I needed a calculation in a loadscript that would provide the number of months between two dates. This kind of calculation comes up sometimes with supply chain planning data when the difference between the month a sales forecast is developed and the month that the sales forecast is meant to be applied, sometimes called a monthly lag, can be used to help measure forecast accuracy. QlikView provides a number of date functions but not one that can calculate the number of months between two dates.

I decided to make my own MonthsBetween date function using a document variable with input arguments. A variable like that can be used like a user defined function. I created a variable in the QlikView document and named it MonthsBetween. This is what I typed for the content of the variable (you, of course, can copy and paste from this blog post):
((Year($2)*12 + Month($2)) - (Year($1)*12 + Month($1)))

The $1 and $2 get replaced by two input arguments (which some people might call parameters) when the variable is used. The arguments must be dates. My loadscript (I was loading data from an Oracle database table) looked something like this:

BUDGET_HIST:
Load BUSINESS_UNIT,
BUDGET_AMOUNT,
CREATE_DATE,
APPLY_DATE,
$(MonthsBetween(CREATE_DATE, APPLY_DATE)) AS MONTH_LAG;
Select * from BUDGET_HIST_TABLE;

For example, if the CREATE_DATE is 1-APRIL-2016 and the APPLY_DATE is 1-JULY-2016 then MONTH_LAG is 3. If the second date is later than the first date then the result will be a positive integer number of months. If the two dates are from the same month then the result is a zero. For my data, the APPLY_DATE is always later than or equal to the CREATE_DATE so MONTH_LAG is always 0 or greater. If CREATE_DATE was later than APPLY_DATE then MONTH_LAG would be a negative number.

The calculation is nothing special -- it must have been re-created thousands of times. But, putting it into a variable makes the calculation easy to use in many places and easy to share. Test it out in a chart expression or advanced search expression. 

When a variable contains expression code like that and the variable might be used as part of a larger expression in a chart, then it is a good idea to enclose the entire variable contents within parentheses as I did above. It helps prevent QlikView from processing the operators in the larger expression in the wrong order.


★★★  

How Many Days Since That Document Reloaded?

I was challenged to write a simple blog posting. Here it is, although it still has some code in it.

A recent project required a document to “know” how many days ago a separate .qvw document had been reloaded. This statement in the loadscript uses the pathname to the separate document and puts the answer to “how many days ago” in the variable vDaysAgo:

LET vDaysAgo = Now() - FileTime('e:\ventures\PriceAnalysis.qvw'); 

  ★★★

  

Testing the Aggr Function Used in an Expression

I’m often asked to take a look at someone's QlikView document with the explanation that “the expression doesn't work”. If it is a complex expression with multiple parts or multiple functions I suggest taking each part of the expression and testing it to make sure it is performing the expected function. When one of the parts is something simple like  sum(InvoiceAmt)  then most people get the idea that they can put that little part of the expression into a temporary chart column or text box and then, based on the current selections and/or chart dimension, check that the function is really showing the sum of the invoice amounts.

If the part of the expression that needs to be tested is an aggr function then you need to know how to check it because the aggr function does not return a single value like the sum function; it returns an array of one or more values. An array of multiple values will usually show up as a null in a chart expression or text expression. If the array happens to have only one value or if all of the values in the array are exactly the same then you will be able to see the result of the expression but its no guarantee that the function is working as intended.

In order to test the aggr function itself, I recommend surrounding the aggr function with a concat expression that will help you analyze the array of the values returned by aggr. For example, if you had a straight table chart with a dimension of geographic region, you might have an expression like this to calculate average sales rep invoice amount totals:
  Avg( Aggr( sum(InvoiceAmt), SalesRep) )

In order to view the values returned by the aggr function, you could create a temporary column and copy and paste the aggr portion of the original expression and surround it with a concat function that might look like this:
  Concat( Aggr( sum(invoiceamt), SalesRep), ' / ' )

That would create a text string showing the individual values in the array separated or delimited by ' / '
Another useful delimiter is ', ' which creates a text string showing the individual values separated by commas.

When using this technique with a chart column there might not be enough space to show all of the array values separated by the delimiter. If that happens, right-click on the chart cell you would like to examine and choose Copy to Clipboard - Cell Value (in a text box you may right-click and choose Copy to Clipboard - Text) and then paste the value into a text file or spreadsheet or other convenient place to review a long text string.

If the array of values you see using this method is what you expected then check out all of the other sections of your expression – if all of them are working as you expect then assembling them into a single expression should work in your application. I often recommend building and testing each of the parts as a development technique when building a complex expression.

One other method for examining the array of values returned by the aggr function is to use it in a temporary chart and use the aggr function as a calculated dimension. A calculated dimension is, by definition, an array of values and it may help you review the values. 

_______________ ___________________ ___________________

Note to Santhosh:   You can remove leading zeros from a numeric identifier like a SAP 18-digit material number in the loadscript with a line like this:
   replace(ltrim(replace(MATNR,'0',' ')),' ','0') as MATNR_NO_ZEROS,

If you foresee the need to do a left join load using the original identifier then keep it as a separate field. If you would like to turn this into a user-defined function like I wrote about three years ago (search on "qlikview maven user-defined function") then do this:

1. Create a variable named DropZeros
2. Paste this into the variable:  replace(ltrim(replace($1,'0',' ')),' ','0')
3. Now, you have something that works like a user-defined function. It could be used in your loadscript line like this:

   $(DropZeros(MATNR)) as MATNR_NO_ZEROS,

★★★

Oracle Hint in the Loadscript

People who load data from an Oracle database into QlikView may sometimes want to use an Oracle hint in the SQL query code. An Oracle hint tells Oracle about how you would like it to plan and execute the query in order to get faster, more efficient execution.

The Oracle hint syntax is actually a comment embedded in the SQL query. For example, the hint to tell Oracle to use up to four parallel processors might look like this in the first line of the query:
SELECT /*+ parallel(4) */ CUSTOMER, SUM(ORD_QTY) FROM   . . .

If you simply code a query like that in the loadscript you won’t get an error or a warning, the query will return data, but you won’t get the performance improvement you were hoping for either. That’s because QlikView doesn’t ordinarily pass comments through to the SQL processor. Oracle would never see your hint.

What is needed, is for you to tell QlikView not to strip out the comments before passing the query into the SQL processor. Code a line like this before your loadscript table definition:

Set StripComments = 0;

ORDER_TABLE:

SELECT /*+ parallel(4) */ CUSTOMER, SUM(ORD_QTY) FROM   . . .

The StripComments variable is a system variable that controls QlikView behavior. Setting the variable to zero tells QlikView not to strip out the comments. It is a good practice to turn StripComments back on after the query with a line like this:
Set StripComments = 1;

One thing to look out for is any other comments in your code. If you have a regular loadscript comment //(a comment beginning with two slashes) mixed in with your SQL code then it will cause a SQL error when it gets passed in to the SQL processor.

★★★

Maven's Sliding Window

Expanding on the example from last time about loading unstructured text data (to read about it, search on QlikView Maven Loading an Unstructured Text File); here’s one way to examine the data. I call it Maven’s Sliding Window. My requirement was to design a method that would allow a straight table to review the rows of text even though there may be a million rows or more. It had to work even though there may be a special selection applied (for example, a selection of all rows containing the word “program”). The straight table had to show just a very small subset of the rows at any one time to avoid the memory issue that would occur if the straight table tried to show many thousands of rows.

[If there was no selection already applied then this task is easy and no special object is required—just select a contiguous group of 20 rows and you can page though the data 20 rows at a time with the Page Up or Page Down keys on your keyboard.]

First, create a variable named vFinger with a current value of 1 and a constraint so that it contains positive integers. Now, create a Slider Object to update the value of vFinger. In the General tab set Mode to Single Value, set Min Value to =min(recno), set Max Value to =max(recno), click the static step box and put 1 in the static step box. I made the Caption tab title say “Window Pointer”. Make the slider have a vertical orientation to help make the usage a little more intuitive.

Now, set up a straight table object. Set up a calculated dimension named recno with an expression like this:
 =If(aggr(rank(-if(recno>$(vFinger),recno)),recno)<=20,recno) 

And set up one expression defined simply as  logtext  (remember that we are still working with the data loaded in the example from 6May2013)
On the Sort tab specify that the dimension recno is sorted ascending numeric.
In my simple example, I made the object title a text expression like this:
  ='Sliding Window Text from $(File_Pathname):'
so that the title uses the variable containing the file pathname to show where the data came from.

That gives us a straight table that is showing only the records surrounding the record number specified by the vFinger variable. Using the slider that controls vFinger controls which section of text appears in the straight table. You could make the “window” bigger by changing the numbers in the calculated dimension.

Add a Page Down button that updates the value of vFinger to move the window “down”. The metaphor of a sliding window means that sliding down goes toward larger recno values and sliding the window “up” goes toward the lower recno values. The button action would be Set Variable to update the vFinger variable. The action Value would be  =max(If(aggr(rank(-if(recno>$(vFinger),recno)),recno)<=20,recno)) 
That adds to the vFinger variable while staying within the recno values that are selected.
A similar Page Up button would contain a Set Variable action to update vFinger with a value like this  =min(If(aggr(rank(if(recno<$(vFinger),recno)),recno)<=20,recno)) 

If I’m working with long text records then I often set up slider objects to control which text columns appear in the straight table. For example, you could set up a slider with horizontal orientation to control the value of a variable named startcol and a slider to control a variable named endcol. Then, in the expression, instead of simply specifying logtext, code this text expression:  Mid(logtext,startcol,(endcol-startcol)+1)
That will allow you to control which text columns appear in the straight table and you can slide that part of the “window” left and right.

Note that you can save the document and later when you re-open it, the sliding window will still show the same group of records since it is driven by variable values that are saved along with the document.

My example here had a record number field to use as a dimension for the straight table. But, any data that forms a series would work just as well; for example, dates, time of day or timestamps, degree/minutes/seconds of longitude, hours of operation of a machine, measurements from the Hadron Collider, etc. 
The next blog post will discuss using the power of QlikView selection and set analysis to help us examine the text data.


★★★

Loading an Unstructured Text File

This is the start of a series of postings about dealing with unstructured text data. I've used QlikView to help analyze message log files and error files and even program source code – there are some things QlikView can do that Notepad style text editors cannot.
First, we need to load the data. It isn’t always a completely unstructured file like my example, often the text is one or more fields inside a structured file or database.  There are various ways to load a text file; this is what I usually put into the loadscript:
TEXTFILE:
LOAD 
RecNo() as recno,
@1 as rectext
FROM
[$(File_Pathname)]
(txt, no labels, delimiter is \x7, no quotes); 

That code will load each record from the text file into the rectext field. I use a variable for the file pathname because I usually use a loadscript like this with a document where the user can supply a filename through an inputbox. Alternatively, you could add browse-for-file functionality like I wrote about in March 2009 (search on QlikView Maven Browse-for-File Macro Button). Because the data is unstructured and the document objects are meant to be used with unstructured data, the document works well as a general-purpose tool that can be used with many types of data in varying situations. 

I defined the file as though it is delimited and made the specified delimiter an uncommon character I don’t expect to find in the data. 

The record number, loaded as recno, from the input file is a useful piece of data and we’ll be using it in some of the example objects over the next few postings.

★★★

Update Document Variables Directly From Excel Spreadsheet

A few years ago I published a blog entry that showed how to copy all of the variables and their values from a QlikView document into an Excel spreadsheet (search on QlikView Maven Listing All Variables and Contents). It was very useful for me, I use it all the time especially when I want to search or sort the variables. Here is the companion to that piece - it is an Excel macro that will copy the data from your spreadsheet into the document variables of a QlikView document. I will say that again - it is an Excel VBA macro (not a QlikView macro).

Copy and paste to install the macro into your Excel spreadsheet and build a button to execute it. You will find it convenient to insert it into the same Excel file that you use for the variable listing I mentioned above. The macro will use a browse-for-file feature so you can select the QlikView document file you want to update. Then, the macro will go down the rows of your active worksheet taking variable names from column A and variable contents from column B and updating the document until it hits an empty cell in column A. It will then tell you how many variables it has processed and ask if you want to save the document. It starts from row 4 of your  worksheet to make it compatible with the earlier spreadsheet macro but you can change the row that it starts with - look for the comment in the middle of the macro.

As always, make a backup copy of your files before running a macro like this. Here's the macro code:


Sub Update_QV_Variables()
'------------------------
'This Excel macro will ask you to select a QlikView document file and
'then it will insert/updateput the variable names and contents from
'your current worksheet into the QlikView document.
'Variable name must be in column A and variable contents in column B.
'The process goes down the rows until it hits an empty cell in column A.
'--QlikView Maven, April 2013
Dim oQV, oDoc, oVars, oTempVar, oThisVar, qv_fn, i, rowlmt, start_row, varctr, x

'Get the QlikView document pathname
qv_fn = Application.GetOpenFilename("QlikView Report File (.),*.*", , "Select a QlikView .qvw Report File", False)
If qv_fn = 0 Then
  Exit Sub
  End If

'Open the QlikView document file
On Error Resume Next
Set oQV = CreateObject("QlikTech.QlikView")
If Err.Number > 0 Then
  x = MsgBox("Sorry, I can't find QlikView", vbOKOnly, "Update_QV_Variables")
  Exit Sub
  End If
Set oDoc = oQV.OpenDoc(qv_fn)
If Err.Number > 0 Then
  oQV.Quit
  x = MsgBox("Sorry, I can't find the QlikView document", vbOKOnly, "Update_QV_Variables")
  oQV.Quit
  Exit Sub
  End If
Set oVars = oDoc.GetVariableDescriptions
If Err.Number > 0 Then
  oQV.Quit
  x = MsgBox("Sorry, I can't find the QlikView document", vbOKOnly, "Update_QV_Variables")
  Exit Sub
  End If
On Error GoTo 0

start_row = 4 'this is the first spreadsheet row with variable data - change as needed
rowlmt = ActiveSheet.UsedRange.Rows.Count
varctr = 0

'Loop through the rows
For i = start_row To rowlmt
  varname = ActiveSheet.Cells(i, 1).Value
  If Len(Trim(varname)) = 0 Then
    Exit For
    End If
  varctr = varctr + 1
  varcontent = ActiveSheet.Cells(i, 2).Value
  Set oThisVar = oDoc.Variables(varname)
    If oThisVar Is Nothing Then
    'must need to create variable
    oDoc.CreateVariable varname
    Set oThisVar = oDoc.Variables(varname)
    End If
  oThisVar.SetContent varcontent, True
  Next

'Finish up
x = MsgBox(varctr & " variables inserted into " & qv_fn & ". OK to Save?", vbOKCancel, "Update_QV_Variables")
If x <> 2 Then
    oDoc.Save
    End If
oDoc.CloseDoc
oQV.Quit
End Sub

★★★

More on Copy Variables From One Document to Another

Here's an alternate version of that macro from last time. This macro can be copied into the module area of a QlikView document (I call mine tookit.qvw) and, when executed, it will copy variables from an external document to another external document. I find this one handier for the kind of work I do.

This document will ask you for the pathname to a source QlikView document containing the variables you want to copy. Then it will ask for the pathname to a destination document that will have the copied variables inserted. If either of the documents requires a password you will be asked for the password as the macro opens the documents. There's no error checking but the most common error for this macro is entering an invalid pathname. After the variables are copied, the macro will ask if it is OK to save the destination document. Remember, as always, to make a backup copy of your documents before testing macros like this.

Here's the code that you can copy and paste into your own toolkit document:


Sub Copy_Variables_Doc_to_Doc
'----------------------------
' /* Copy Variables From One QV Document to Another */
' /* QlikView Maven, February 2013 */
Dim objQV, objSource, objDest, objSourceVar, objDestVar 
Dim objVars, varcontent, objTempVar, varname, i, varctr
'initialize
fn_source=inputbox("Enter pathname to source document containing the variables you want to copy:","Enter Source Document Pathname")
if trim(fn_source)="" then 'no entry or cancel
  exit sub
  end if
Set objSource=Application.OpenDoc(fn_source) 

fn_dest=inputbox("Enter pathname to destination document where variables are to be inserted:","Enter Destination Document Pathname")
if trim(fn_dest)="" then 'no entry or cancel
  exit sub
  end if
Set objDest=Application.OpenDoc(fn_dest) 

'Loop through the variables
set objVars=objSource.GetVariableDescriptions
varctr=0
for i = 0 to objVars.Count - 1
  varctr=varctr+1
  set objTempVar = objVars.Item(i)
  varname=Trim(objTempVar.Name) 
  Set objSourceVar=objSource.Variables(varname) 
  varcontent=objSourceVar.GetRawContent
  'display the variable to check on progress if needed
  'msgbox(varname & " = " & varcontent)

  'update the value of variable in destination document
  Set objDestVar=objDest.Variables(varname)
  If objDestVar is nothing then
    'must need to create variable
    objDest.CreateVariable varname
    Set objDestVar=objDest.Variables(varname)    
    End If

  objDestVar.SetContent varcontent,true

next 'end of loop 

'we're done, show a message and close down
x=msgbox(varctr&" variables copied from "&fn_source&" to "&fn_dest&"."&chr(13)&"OK to Save?",vbOKCancel,"Copy_Variables_Doc_to_Doc")
if x=vbOK then
    ObjDest.Save
    End if
objDest.CloseDoc 'comment out this line if you'd like dest document to stay open
objSource.CloseDoc
set objSource=nothing
set objDest=nothing
set objVars=nothing
set objTempVar=nothing
set objSourceVar=nothing
set objDestVar=nothing
End Sub  'end of Copy_Variables_Doc_to_Doc


★★★

Macro to Copy Variables In From Another Document

My preference when I need to copy variables from one QlikView document to another is to use a small Windows script to do the copying (I wrote about it in 2008 - search on "Copying Variables From One QlikView Document to Another"). But, I know there are some people who can't use a Windows script because of corporate or I.T. rules. Here's a QlikView macro that you can insert into your module area that will copy in all of the variables from another document. 

This macro has you entering a pathname for the document into an input box. For anyone in the advanced class you can replace that with a browse-for-file feature if you think it is worth the time (I wrote about it - search on "Browse-for-File Macro Button"). In the next blog posting ("More on Copy Variables From Document to Another") I show a macro that can be installed once in a toolkit document and it will copy variables from a specified external document to another external document.

Here's the macro code. Try it out - you can copy and paste the text you see in this blog. Remember, as always, to make a backup copy of your document before testing macros like this. To run the macro, create a button for it or for one-time use just click the Test button in the module editor. There's no error checking but the most common error for this macro is entering an invalid pathname. If your source document requires a password then you will be asked for the password when the macro opens the source document.

Sub Copy_Variables
' /* Copy Variables In From Another QV Document */
' /* QlikView Maven, February 2013 */
Dim objQV, objSource, objDest, objSourceVar, objDestVar 
Dim objVars, varcontent, objTempVar, varname, i, varctr
'initialize
fn=inputbox("Enter pathname to source document containing variables you want to copy:","Enter Pathname for Source Document")
if trim(fn)="" then 'no entry or cancel
  exit sub
  end if
Set objSource=Application.OpenDoc(fn) 
set objVars=objSource.GetVariableDescriptions
varctr=0

'Loop through the variables
for i = 0 to objVars.Count - 1
  varctr=varctr+1
  set objTempVar = objVars.Item(i)
  varname=Trim(objTempVar.Name) 
  Set objSourceVar=objSource.Variables(varname) 
  varcontent=objSourceVar.GetRawContent
  'display the variable to check on progress if needed
  'msgbox(varname & " = " & varcontent)

  'update the value of variable in current document
  Set objDestVar=ActiveDocument.Variables(varname)
  If objDestVar is nothing then
    'must need to create variable
    ActiveDocument.CreateVariable varname
    Set objDestVar=ActiveDocument.Variables(varname)
    End If

  objDestVar.SetContent varcontent,true

next 'end of loop 

'we're done, show a message and close down
msgbox varctr&" variables copied from "&fn
objSource.CloseDoc
set objSource=nothing
set objVars=nothing
set objTempVar=nothing
set objSourceVar=nothing
set objDestVar=nothing
End Sub  'end of Copy_Variables

★★★

Referring to a Column Label When the Label is a Text Expression

Last week, I added an enhancement to a QlikView document that had been in use for over two years. My task was simply to add a column to a straight table that would have an expression like this:  =[column1] - [column2]. It should be pretty simple. 
But the two columns I needed to refer to had text expressions using variables in their column labels. The two column labels looked like this:
=abv_PRICE&' '&lbl_Cmpr&' '&abv_EUAVG
=abv_COST&' '&lbl_Cmpr&' '&abv_EUSTD 

It took a few tries to get the syntax right. I didn't change the existing columns but I copy and pasted their label expressions into my column expression and then modified the syntax and added dollar sign expansion. The expression for my new column still refers to the two other columns using their column labels within square brackets; it looks like this: 
[$(='$(abv_PRICE) $(lbl_Cmpr) $(abv_EUAVG)')]-[$(='$(abv_COST) $(lbl_Cmpr) $(abv_EUSTD)')]

I hope this might help someone with a similar problem to solve. 

★ ★ ★

Macro Copies Value From a Chart into a Variable

Here's an example of a method that will allow a QlikView macro to look at a value inside a sheet object like a straight table and put the value into a variable. Then the variable can be used in an expression like a text box or small chart. Most of the time, something like this could be accomplished with an expression in the variable that produces the same value as what you see in the chart. But a macro would be useful when duplicating the chart value with an expression would be difficult because of varying sort sequences or calculated dimensions in the chart, etc.

This is the macro code:

Sub Get_XTOTAL
 Application.WaitForIdle
 set cell=ActiveDocument.GetSheetObject("CH45").GetCell(1,7)
 ActiveDocument.Variables("vXTOTAL").SetContent cell.text, True 
 set cell=nothing
End Sub

This example macro is extracting a value from the totals row of the chart where the object ID is CH45 (look in the General tab of the chart properties to find the object ID). The number arguments for the GetCell method are row and column; row 1 and column 7 in this example.
Then the macro updates the value in the document variable named vXTOTAL. When written as in this example, the variable must already exist, so create the variable before executing the macro.
When I use a macro like this, I set up a trigger to execute the macro as needed, usually when a selection changes.

★ ★ ★

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.

★ ★ ★

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.

* * *

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.

* * *

Capture Variable Values in the Loadscript

I often put statements in the loadscript that record current conditions as document variables in case the information might be useful someday when checking into a problem or simply to document the origin of the data. Here’s a few that have proved useful:

let reload_time = now();
let user = OSuser();
let computer = ComputerName();
If IsPartialReload() then
   let partial_reload_time = now();
Else
   let partial_reload_time = '';
End If

Those variables can be displayed on the title tab or in a corner of the main tab.

You can capture information from the data that is being loaded and store it in a variable. This is useful for control information that would be displayed on the title page like the system information above. It can also be useful to control If statements and Loops and Where clauses in the loadscript.

Here’s an example that reads a date from an Oracle table and stores it in a QlikView document variable so it can be displayed in a chart title:

//Find OHPOST date and store in a one-row table
DUNK_OHPOST:
SELECT TO_CHAR(MAX(OHPOST),'MM/DD/YYYY') AS DUNK_OHPOST FROM SKU;

//Now, put the value into a document variable
Let DUNK_OHPOST=Date(Peek('DUNK_OHPOST',0,'DUNK_OHPOST'));
Drop table DUNK_OHPOST;

That Peek function takes 3 arguments: fieldname, row number, and table name. For a one-row table use zero to indicate the first row. If that is a unique field name then you could also use the FieldValue function to retrieve the value from the first field value.

In older QlikView versions you could use the Peek function in a text object but that isn't allowed any longer. So, store the values in document variables and you can display them in text object or chart titles. Consider displaying data values that tell the report user something important about the data; such as earliest or latest posting dates, high or low order numbers, current fiscal period, currency conversion factor, source application version number, security or classification warning, etc.

Variables in the .bat script

This series of blog postings about variables has probably been of interest mostly to technical folks concerned with automating QlikView documents. This one is also like that only more so. Here’s some automation wisdom that you won’t find anywhere else:

We have a set of reports where the document variables contain all the file pathnames and database access names. Loading of the reports is handled by Windows .bat scripts. We saw that the .bat scripts also needed to be able to use flexible file pathnames and database access. The variables and their values are stored in a comma-delimited file (a .csv file). The .bat scripts load their variables from the same file that the QlikView documents use to load their variables.

This is a .bat script command that will load DOS variables from a comma-delimited file:

FOR /F "skip=1 tokens=1-2 delims=," %%a IN ('type ..\Report_Control\RPT_VARS.csv') DO set %%a=%%b

That one line reads a comma-delimited text file named RPT_VARS.csv that is stored in a folder named ..\Report_Control
Skip=1 tells it to skip over the first line of the file (because it contains column titles).
The text file is simple. It looks something like this:

Variable Name,Variable Value
RPT_DBNAME,INVT_PROD
RPT_INPUT,E:\CURRENT\DATA\
RPT_OUTPUT,\\AMSERV28\PROJECTS\
RPT_LOG,C:\LOGS\BATCHLOG.TXT

Within the .bat script the variables are used between percent signs. So a line in the script might look like this:

echo %date%,%time% QV Batch started >> %RPT_LOG%

---

One of the reasons we stored all of the pathnames and access parameters in an external file that could be turned into variables is that it simplified the change control or promote to production process. We could develop a QlikView report and the associated scripts on a development server and then easily move the report and scripts as files to the QA server or production server without any changes. The programs would run correctly after being promoted to QA or production because they picked up the appropriate pathnames and database access variables info from the comma-delimited file stored on the new server.

We name a lot of our files with a yyyymmdd datestamp in the filename, for example “20081126_NorthRegion.dat”. It’s easy to create today’s yyyymmdd date in the .bat script as a variable with this script line:
FOR /F "tokens=1-4 delims=/- " %%A in ('date/T') do set TTDATE=%%D%%B%%C

Then it can be used in the .bat script as part of a filename like this:
COPY CURRENT.DAT %TTDATE%_NorthRegion.dat

Loading Variables Via Loadscript

You have two basic alternatives if you want to load document variables from a database or file. You can load them as part of the loadscript as you load data for reporting, or, you can load them through a macro script either within the QlikView document or external to the document.

In order to load variables from a database or file you must first have the variable names and the variable contents defined in the database table or in the file. The file might be a spreadsheet or text file but it must have one column or field defined for the variable names and a second column or field defined for the variable contents.

Within the QlikView loadscript you first load the variable names and contents into a QlikView table. From a database you might code it something like this:

RPT_VARS:
SQL SELECT
VAR_NAME,
VAR_VALUE
FROM REPORT_VARIABLES;

Then insert some code like this to convert the table into document variables:

Let RowCount = NumMax(NoOfRows('RPT_VARS'),0)-1;
For i=0 to '$(RowCount)'
  Let TempVarName = peek('VAR_NAME',$(i),'RPT_VARS');
  Let TempVarValue = peek('VAR_VALUE',$(i),'RPT_VARS');
  Let $(TempVarName) = '$(TempVarValue)';
  next

//If the table is no longer needed it can be dropped
Drop table RPT_VARS;

Note the NumMax function. That is necessary if the RPT_VARS table were ever empty; a null RowCount variable will cause a never-ending loop.

At work we have some reports where all of the expressions and titles and field labels are handled with variables. This allows us to use the same report at several different companies but the appearance and functionality of the report is tailored to local terminology and business practice. Another way to use this concept might allow the same report at a single company to look and work differently when it loads data from different databases that contain different sets of customized variables. So, a standard sales report could look different for the Commercial Division than it does at the Baked Goods Division.

Listing All Variables and Contents

[Author's note:  You can see information about a different method for accomplishing this same thing. Search on: QlikView Maven Utility That Can Extract and Show All of the Variables ]

After last weeks blog posting I got some questions about how you might list out all of the document variables and their contents. It can be a useful thing for reports that depend on variables for their functionality and useful for corporate developers who must document report functionality.

Below is a Microsoft Excel macro that could help you. When you run this macro it will ask you to select a QlikView report file and then it will copy all of the document variables and their contents into the current worksheet. Once the data is in a spreadsheet you can sort it, print it, search it for particular fields or functions, or write it out as a text or tab-delimited file. In a future blog posting I'll show how variables can be loaded into a report from a spreadsheet which, along with the macro below, will give you a method for sharing and maintaining variables.

You can get a copy of the spreadsheet with the macro installed and a button that runs the macro at this website:  [Author's note - old site is not working anymore].  The spreadsheet on the website also includes a button that will list all variables from a currently open QlikView document (sorry about the advertisements on the site - just click on the spreadsheet downloads).
Here's the Excel macro code. You can copy and paste the macro code from this window and put it into your own spreadsheet if you want to build your own.

Sub List_QV_Variables()
'This Excel macro will ask you to select a QlikView Report File and
'then it will put the variable names and contents into your current worksheet
'--Tim Benoit, Nov 2008
Dim oQV, oRpt, oVars, oTempVar, oThisVar, qv_fn

'Get the QlikView report pathname
qv_fn = Application.GetOpenFilename("QlikView Report File (.),*.*", , "Select a QlikView .qvw Report File", False)
If qv_fn = 0 Then
  Exit Sub
  End If

'Write out some heading text
ActiveSheet.Cells(1, 1).Formula = "QlikView Variables List"
ActiveSheet.Cells(2, 1).Formula = "Report pathname:"
ActiveSheet.Cells(2, 2).Formula = qv_fn
ActiveSheet.Cells(3, 1).Formula = "Variable Name"
ActiveSheet.Cells(3, 2).Formula = "Variable Content"

'Open the QlikView report file
Set oQV = CreateObject("QlikTech.QlikView")
Set oRpt = oQV.OpenDoc(qv_fn)
Set oVars = oRpt.GetVariableDescriptions
'Loop through the variables
For i = 0 To oVars.Count - 1
  Set oTempVar = oVars.Item(i)
  varname = Trim(oTempVar.Name)
  Set oThisVar = oRpt.Variables(varname)
  varcontent = oThisVar.GetRawContent
  'Write data into worksheet cells
  ActiveSheet.Cells(i + 4, 1).NumberFormat = "@"
  ActiveSheet.Cells(i + 4, 1).Formula = varname
  ActiveSheet.Cells(i + 4, 2).NumberFormat = "@"
  ActiveSheet.Cells(i + 4, 2).Formula = varcontent
  Next

'Close QlikView
oRpt.CloseDoc
oQV.Quit
End Sub