Vizlib Home
Welcome
Login

Vizlib Writeback Table / Input Form - Expressions

Expression Editor


Important: This article can also be used to work with expressions in Vizlib Input Form


Vizlib Writeback Table
is supported by an expression editor where you can create and maintain Qlik Sense expressions to generate your column data. You can use expressions to define column properties, add new information to your writeback table. This article will use expressions to display a results in a calculated column type. 


You can open the expression editor by clicking on the button where it's displayed in the property panel. Figure 1 selects Calculated Column, scrolls to the Type section in the property panel and opens the editor for the Calculation function. 


Figure 1: Expression Editor


The Calculated Column utility type uses 2 calculation methods, Simple or Qlik Expression.

  • Simple - the calculation is completed using standard arithmetic operations. 
  • Qlik Expression - the calculation is completed using Qlik Sense functionality (the Qlik Engine). 


The syntax used for both methods replaces the value Column with the value held in the column for each row, so writing Column(6) in an expression represents the value held in column 6. 


The example in this article will use the Qlik Expression method. We're looking to show a result for the expression 

Sum(Column(9)+Column(10))

which should give the result shown in Figure 2, where Calculated Column displays the sum of the previous 2 columns. 


Figure 2: Calculated Column Result


Note on Column / Field Numbering: For expressions, Column parameters are numbered in their order of appearance in the Writeback Table or Input Form, rather than the property panel. If you change the table or form column order, you may need to change the expression Column parameters. 

  • Writeback Table - columns numbered left to right. Column(1) is the first field from the left. 
  • Input Form - fields numbered top to bottom. Column(1) is the first field from the top. 



Evaluation and Errors


Before you see the expression result displayed in Writeback Table, the expression text undergoes evaluation to check for errors. We're going to use the words formula, extension and engine in the article descriptions to keep them simple, there's a list of definitions here.

  • Formula - the text entered in the expression editor. 
  • Extension - evaluation using Vizlib functionality (Vizlib Writeback Table in this example).  
  • Engine - evaluation using Qlik Engine. 


For an expression to successfully return a result, these steps need to be completed.


  1. The formula is analyzed via the extension, all Column values are replaced with values from the table. Column(9) and Column(10) in this example would be replaced by numbers held in column 9 and 10.
  2. The formula is sent to the engine for evaluation. 
  3. The engine returns the result, which is displayed. 


Small differences in syntax can make a real difference, and result in an error being returned. For example, when changes to an expression are saved by clicking Apply in the editor, an equal sign appears at the start of the expression.

=Sum(Column(9)+Column(10))

This causes the formula to be sent to the engine without step 1 completing, so the engine tries to evaluate Column(9) and Column(10) and fails, as these are not numeric values. Figure 3 shows the results when the equal sign is included. 


Figure 3: Incorrect Syntax


Similar errors can also be generated when other characters are used, such as 

=Sum('Column(9)'+'Column(10)')
Sum'Column(9)+Column(10)'



Resolving Syntax Errors


If you do return an error like these, the quickest way to resolve it is to manually change the expression without opening the editor. In Figure 4, removing the sign before Sum in the Calculation text field resolves the error. The table is then saved and displayed in analysis mode. 



Figure 4: Resolve Syntax Error



Further Reading


You can find a list of related articles in the Vizlib Writeback Table Overview





K
Kenneth is the author of this solution article.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.