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