Binding expressions in on-demand template apps

Data bindings in a template app specify which data from a corresponding selection app is used to formulate the queries issued when loading data into an on-demand app.

The basic form of binding expressions--$(od_FIELDNAME)--can be modified to refine selections and to ensure that the template app loads data correctly.

Binding a large number of selections from a field

The Qlik engine limits the number of values that can be concatenated together in a single LET statement. To avoid the limitation, an INLINE LOAD statement can be used to efficiently construct a comma-separated list of values.

First, you must build a script subroutine to process an INLINE table.

SUB BuildValueList(VarName, TableName, ColName, QuoteChrNum)
  IF $(QuoteChrNum) = 0 THEN
    LET LOADEXPR = 'Concat($(ColName),' & chr(39) & ',' & chr(39) & ') AS CombinedData';
  ELSE
    LET CHREXPR = ' chr(' & '$(QuoteChrNum)' & ') ';
    LET LOADEXPR = 'Concat( $(CHREXPR) & $(ColName) & $(CHREXPR)' & ',' & chr(39) & ',' & chr(39) & ') AS CombinedData';
  ENDIF
  _TempTable:
  LOAD $(LOADEXPR) Resident $(TableName);
  Let vNoOfRows = NoOfRows('_TempTable');
  IF $(vNoOfRows)> 0 THEN
    LET $(VarName) = Peek('CombinedData',0,'_TempTable');
  ENDIF
  drop table _TempTable;
  drop table '$(TableName)';
END SUB;

The binding should then be written using an INLINE table to create a structure for the field values that will load regardless of the number of values.

SET ORIGIN='';
OdagBinding:
LOAD * INLINE [
VAL
$(odso_ORIGIN){"quote": "", "delimiter": ""}
];
SET ORIGIN_COLNAME='ORIGIN';
CALL BuildValueList('ORIGIN', 'OdagBinding', 'VAL', 39);

The $(od_ORIGIN) {"quote": "", "delimiter": "") expression will be replaced by a list of ORIGIN field values from the selection app, separated by line breaks. If the ORIGIN field contains the three values BOS, JFK, ORD, then the expanded INLINE table looks as follows:

SET ORIGIN='';
OdagBinding:
LOAD * INLINE [
VAL
BOS
JFK
ORD
];
SET ORIGIN_COLNAME='ORIGIN';
CALL BuildValueList('ORIGIN', 'OdagBinding', 'VAL', 39);

The value of the ORIGIN variable following the call to BuildValueList will be:

'BOS','JFK’,'JFK'

Binding optional and excluded values

Sometimes it is necessary to create a filter condition in the query for the on-demand template app's script using fields that are not directly selectable in the selection app. For example, sales records might use a region code like REGION_CODE while the selection app uses a more recognizable REGION_NAME field for making region selections. The selection app can have a model that uses a separate table to associate REGION_CODE with REGION_NAME so users can select values from REGION_NAME to control the selection state.

While selecting values of REGION_NAME causes those values to be placed in the selected state, the values of REGION_CODE are only in the optional state, that is, white rather than green. Futhermore, if the design of the selection app's sheets excludes REGION_CODE from its set of filter panes, there is no way to have the bind expression $(od_REGION_CODE) in the script of the on-demand app expand to the list of selected regions because the REGION_CODE values will never actually be selected, that is, made green.

To handle this situation, there is additional syntax to more precisely control which selection state values are used in each data binding. The od_ prefix in the field name portion in every on-demand bind expression can include a combination of letters to denote whether the values to be used in the binding are those taken from the selected state and the optional state. The valid combinations, using the REGION_CODE example, are:

In the case of the on-demand app for sales data example, the following data binding expression ensures that either the selected or optional values of REGION_CODE are included in the REGION_CODE binding:

$(odso_REGION_CODE)

Binding numeric values

When the data to be bound to the on-demand app consists of numbers instead of strings, it is useful to disable the quote wrapping behavior on the numeric fields. For example, if the sales records include a numeric DAY_OF_WEEK column and you want the user of the selection app to choose arbitrary combinations of DAY_OF_WEEK, you would augment the aggregation query used for loading the selection app to include DAY_OF_WEEK in both the SELECT list as well as the GROUP BY list. If quotation marks are wrapped around DAY_OF_WEEK values when they are selected, a runtime query error could result if the database does not support automatic type conversion from string to numeric.

To handle this situation, there is a syntax suffix that can be added to the end of the FIELDNAME portion of the bind expression to force the field binding to use the numeric values from the selection app rather than string values. The suffix is _n as in the following WHERE clause:

WHERE DAY_OF_WEEK IN ( $(od_DAY_OF_WEEK_n) );

This would expand to:

WHERE DAY_OF_WEEK IN ( 1,2 );

Requiring a certain number of selections

In some situations, it may be necessary to require that the on-demand app query contain a specific number or range of values for a specific field. For example, if the on-demand app's query contains a BETWEEN clause to obtain all sales between a start and end date, the bind expression for the YEARQUARTER field can have a suffix syntax of [2] that will require exactly two values be selected for YEARQUARTER, as in:

$(od_YEARQUARTER)[2]

The on-demand app navigation point on the selection app will remain disabled as long as there are not exactly two values of YEARQUARTER selected. A message will display to indicate that exactly two values of YEARQUARTER must be selected.

Selection quantity constraints create a prerequisite linkage between the selection app and the on-demand app. This is different from bind expressions that do not use quantity constraints. For example, when the template app's script contains a bind expression without a quantity constraint, as in:

$(od_MYFIELD)

there is no requirement that the selection app contain a field named MYFIELD nor for there to be any selected values of that field if it does exist. If the selection app does not contain a field named MYFIELD or if the user simply neglects to make any selections from it, the on-demand app navigation point can still become enabled when other selections are made to fulfill the record-limit value condition.

If on the other hand, the bind expression is:

$(od_MYFIELD)[1+]

there are now two requirements placed on the selection app:

• The selection app must contain a field named MYFIELD.
• The user must select at least one value for MYFIELD.

This type of bind expression must be used carefully because it limits which selection apps can be used with the template app. You should not use this quantity constraint on bindings of a template app unless you are certain you want to impose that selection quantity requirement on all selection apps that link to that template app.

To perform the data binding process, the On-demand app service uses a string substitution approach that is insensitive to comments in the script. This means you should not use bind expressions in comments unless you want those comments to contain the list of bound values following app generation.

Other quantity constraints are possible. The following table summarizes the different combinations of selection quantity constraints.

Changing the value quotation and delimiter characters

When a list of values from a field selected in an On-demand selection app is inserted into the script of a template app, the values are surrounded by single quotation marks and separated by commas. These are the default characters for quotations and delimiters. These values can be changed in syntax appended to binding statement for each field. For example:

These new values are then used when formulating the list of bind values taken from the selection app. If, for example, the selected values are the first three months of the year, the list would be constructed as:

The default values for the quotation and delimiter characters work for most standard SQL databases. But they might not work for some SQL databases and do not work for many dynamic data sources such as NoSQL and REST. For those sources, you need append this binding expression to change the quotation and delimiter characters.