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.

★★★

Selecting Listbox Values Containing Either of Two Different Pieces of Text

Last week a coworker had a document loaded with data from a database job log table. It was over a million rows of log data that various batch jobs had written into. She wanted to select the rows that represented the start time of jobs and also the rows that represented the end time of jobs. Either one by itself would be easy. We knew that the LOGMESSAGE column contains the text ‘JOB START [‘ for log messages posted by jobs when they start up and the column contains the text ‘FINISH STATUS [‘ for messages posted by jobs when they end. To select either of those types of rows you would just click a listbox for the LOGMESSAGE column and start typing the text and QlikView would automatically select the rows. But, selecting both start message and end messages – that is different, how to do that?

The answer is to use an advanced search expression. For this example, you click on the LOGMESSAGE listbox and then start typing this:  =wildmatch(LOGMESSAGE,' *JOB START [*','*FINISH STATUS [*')

As soon as you type the equal sign, QlikView knows you are entering an advanced search expression. QlikView then tests the WildMatch function for each possible value of LOGMESSAGE and if the function is true then the column value is shown in the listbox and when you click [enter] the values are selected. It worked great for our log data problem- all of the start and end log messages were selected and shown in the chart along with their job start timestamps and job end timestamps.

The asterisks in the WildMatch expression are wild card characters that represent any series of characters. We could have added other wildcard matching strings too if we needed them. For example, if the job end messages had two different text layouts then we could have used the matching text to select both types of end messages. Despite the fact that the WildMatch expression contains the column name, LOGMESSAGE, it could be used with any of the columns or fields in the document and it would select the values that are associated with the LOGMESSAGE start and end messages.

★★★

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

★★★

Macro to Copy Cycle Group From One Document to Another

Keeping with the toolkit theme, here's a macro that can copy a cycle group from one QlikView document to another. If you've done much development, you know that it is easy to copy charts and other sheet objects from document to document; but cycle groups cannot be copied and some of them represent a lot of work to completely replicate in another document.

Copy this macro code into the module area of your toolkit document and create a button to execute it. When the macro is executed it will ask for the pathname to the source document containing the cycle group to be copied. Then it will ask for the cycle group name (remember that upper and lower case is important), and then the pathname to the document that the cycle group will be copied into. If either document requires a username or password QlikView will ask for those as the macro opens the documents. As always, make copies of your documents before testing a macro like this.

Here's the macro code you can copy and paste:


Sub Copy_Cycle_Group_Doc_to_Doc
'------------------------------
' /* Copy Cycle Group From One QV Document to Another */
' /* QlikView Maven, March 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 Cycle Group you want to copy:","Enter Source Document Pathname")
if trim(fn_source)="" then 'no entry or cancel
  exit sub
  end if

CGName=inputbox("Enter name of Cycle Group to copy:","Enter Cycle Group Name")
if trim(CGName)="" then 'no entry or cancel
  exit sub
  end if
fn_dest=inputbox("Enter pathname to destination document where the Cycle Group is to be inserted:","Enter Destination Document Pathname")
if trim(fn_dest)="" then 'no entry or cancel
  exit sub
  end if

Set objSource=Application.OpenDoc(fn_source)
Set objDest=Application.OpenDoc(fn_dest) 
Set SrcGrp = objSource.GetGroup(CGName)
Set SrcProps = SrcGrp.GetProperties
Set NewGrp = objDest.GetGroup(CGName)
If NewGrp Is Nothing Then 'check if cycle group exists in dest document
  Set NewGrp = objDest.CreateGroup(CGName) 'doesn't exist so create it
Else
  Set NewProps = NewGrp.GetProperties 'does exist so remove old definition
  for i = 1 to NewProps.FieldDefs.Count
    NewGrp.RemoveField 0
    Next
  End If
Set NewProps = NewGrp.GetProperties
'copy elements of cycle group from source to dest
NewProps.FieldDefs.CopyFrom SrcProps.FieldDefs
NewProps.IsCyclic = SrcProps.IsCyclic
NewProps.Labels = SrcProps.Labels
NewProps.Present = SrcProps.Present
NewProps.SortCriterias.CopyFrom SrcProps.SortCriterias
NewGrp.SetProperties NewProps

'we're done, show a message and close down
x=msgbox(CGName&" copied from "&fn_source&" to "&fn_dest&"."&chr(13)&"OK to Save?",vbOKCancel,"Copy_Cycle_Group_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 for examination
objSource.CloseDoc
set objSource=nothing
set objDest=nothing
set SrcGrp=nothing
set SrcProps=nothing
set NewGrp=nothing
set NewProps=nothing 
End Sub  'end of Copy_Cycle_Group_Doc_to_Doc

★★★

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

★★★

Make a Copy of a Cycle Group

I sometimes build cycle groups with more than a dozen calculated dimension expressions and labels that are text expressions. It used to irritate me when I needed to make copies of those cycle groups (like when I need a very similar group for a chart with two or more dimensions or a separate group for a graph which may be a little different from a group used for a straight table). Copying the cycle group was a time-consuming chore going back and forth copying and pasting.

Here's a macro you can copy into the module code of a QlikView document that will allow you to easily make copies of a cycle group. I got the code for this macro originally on QlikCommunity and modified it to use input boxes to ask for the cycle group names. It has been been very useful - I usually just copy it into a document temporarily when I need to make duplicate cycle groups and remove the macro afterwards. As always, you should make a backup copy of your document before making major updates or running macros that do updates.

Sub RunCopyCycleGroup
'--------------------
CGName=inputbox("Enter name of cycle group to copy:")
if trim(CGName)="" then 'no entry or cancel
  exit sub
  end if
NewName=inputbox("Enter name for copy of "&CGName&":")
if trim(NewName)="" then 'no entry or cancel
  exit sub
  end if
Call CopyCycleGroup(CGName, NewName)
End Sub
'
Sub CopyCycleGroup(CGName, NewName)
'-----------------------------------
' /* Copy cycle group. OK if NewName exists. */
Set SrcGrp = ActiveDocument.GetGroup(CGName)
Set SrcProps = SrcGrp.GetProperties
Set NewGrp = ActiveDocument.GetGroup(NewName)
If NewGrp Is Nothing Then
  Set NewGrp = ActiveDocument.CreateGroup(NewName)
Else
  Set NewProps = NewGrp.GetProperties
  for i = 1 to NewProps.FieldDefs.Count
    NewGrp.RemoveField 0
    Next
  End If
Set NewProps = NewGrp.GetProperties
NewProps.FieldDefs.CopyFrom SrcProps.FieldDefs
NewProps.IsCyclic = SrcProps.IsCyclic
NewProps.Labels = SrcProps.Labels
NewProps.Present = SrcProps.Present
NewProps.SortCriterias.CopyFrom SrcProps.SortCriterias
NewGrp.SetProperties NewProps
msgbox CGName&" copied to "&NewName
End Sub

I have also posted a macro that will copy a cycle group from one document to another, separate document (search on "Macro to Copy a Cycle Group From One Document to Another").

★★★

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.

★ ★ ★

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.