IQ Bot 11.x: Formulas
- 최종 업데이트2022/08/30
IQ Bot 11.x: Formulas
For calculative or comparative validation, in the design view, specify a formula as part of validation options for a selected field or table column.
Prerequisites
You can specify formula validation only when format of field or table column is a number.
- In the Designer, in Preview (See extraction results) view, the formula validation flags errors as expected, to process the training documents.
- Upload the same documents to production. The documents get processed and failed due to the same formula validation.
- In the Validator, the same formula validation continues to flag errors to process the documents.
To specify a formula for validation, perform the following steps.
Procedure
- Select the validation type as Formula from the Validate drop-down menu.
- Type the formula of your choice and click Define to save the formula.
- To write a formula, you can use basic arithmetic, comparative, logical, and functional operations. Validation rules can be built by combining these operations with different fields and table columns.
-
Fill in the fields in the form, as appropriate.
Table 1. Mathematical Operators Operations Description Syntax + Addition Field/Column_Name1 + Field/Column_Name2 - Subtraction Field/Column_Name1 - Field/Column_Name2 * Multiplication Field/Column_Name1 * Field/Column_Name2 / Division Field/Column_Name1 / Field/Column_Name2 Table 2. Comparative Operators Operations Description Syntax == Equal To [Current Field/Column Name] == [expression comprised of one or more field/column name or fixed numeric values]
For example, AMOUNT == MUL(QUANTITY, UNIT_PRICE)
>= Greater than or Equal To [Current Field/Column Name] >= [expression comprised of one or more field/column name or fixed numeric values]
For example, TOTAL_BILL_AMOUNT >= AMOUNT_PAID
<= Less than or Equal To [Current Field/Column Name] <= [expression comprised of one or more field/column name or fixed numeric values]
For example, AMOUNT_PAID <= TOTAL_BILL_AMOUNT
> Greater Than [Current Field/Column Name] > [expression comprised of one or more field/column name or fixed numeric values]
For example, BILL_AMOUNT > 0
< Less Than [Current Field/Column Name] > [expression comprised of one or more field/column name or fixed numeric values]
For example, DISCOUNT_PERCENTAGE < 100
!= Not Equal To [Current Field/Column Name] != [expression comprised of one or more field/column name or fixed numeric values]
For example, DEBIT_AMOUNT != 0
Table 3. Logical Operators Operations Description Syntax && And: Field/Column is valid if all conditions are true
<Current Field/Column Name> <operator 1> <expression 1> && <Current Field/Column Name> <operator 2> <expression 2>
For example, DISCOUNT_PERCENTAGE >= 0 && DISCOUNT_PERCENTAGE <=100
|| Or: Field/Column is valid if any one of the given conditions is true
<Current Field/Column Name> <operator 1> <expression 1> || <Current Field/Column Name> <operator 2> <expression 2>
For example, DISCOUNT_PERCENTAGE >= 0 || DISCOUNT_PERCENTAGE == 'Net'
! Not: Converts a true expression to false and also the other way round
!<expression>
For example, !(AGE < 18) ==> valid when AGE is not less than 18
Table 4. Functional Operators Operations Description Syntax SUM Summation: gives result of addition of one or more field/column/fixed-number values
SUM(n1,n2,....,nN)
For example, SUM(SUB_TOTAL, SERVICE_TAX, EDUCATION_CESS) ==> equivalent to SUB_TOTAL plus SERVICE_TAX plus EDUCATION_CESS
SUB Subtraction: gives result of subtraction of one or more field/column/fixed-number values from the first specified value
SUB(n1,n2,....,nN)
For example, SUB(SUB_TOTAL, TOTAL_DISCOUNT, ADJUSTMENTS) ==> equivalent to SUB_TOTAL minus TOTAL_DISCOUNT minus ADJUSTMENTS
MUL Multiplication: gives result of multiplication of one or more field/column/fixed-number values
MUL(n1,n2,....,nN)
For example, MUL(QTY_IN_BOX, UNIT_PRICE, SHIPPED_BOXES) ==> equivalent to QTY_IN_BOX multiply-with UNIT_PRICE multiply-with SHIPPED_BOXES
DIV Division: gives result of division of one or more field/column/fixed-number values from the first specified value
DIV(n1,n2,....,nN)
For example, DIV(AMOUNT, SHIPPED_BOXES, UNIT_PRICE) ==> equivalent to AMOUNT divide-by SHIPPED_BOXES divide-by UNIT_PRICE
COLSUM Sum of a given Column in a table: gives result after evaluating given expression for each row of specified table and adding them all together
COLSUM("<table-name>", "<expression to evaluate for each row of specified table-name>")
For example, FINAL_TOTAL == COLSUM("LINE_ITEMS", "MUL(QTY, UNIT_PRICE)")
Say there are 3 rows for table LINE_ITEMS, then FINAL_TOTAL should be equal to MUL(QTY1, UNIT_PRICE1) + MUL(QTY2, UNIT_PRICE2) + MUL(QTY3, UNIT_PRICE3)
where, QTY1 is QTY value in row 1, similarly UNIT_PRICE1 is UNIT_PRICE value in row 2, and so on for other rows as well.
Tip: We recommended using functional operators instead of mathematical operators when validating fields and tables for better accuracy.When formulating an expression remember the following:- All function names must be in capital letters.
- All formulas must result in either a true or false validation.
- Field and column names are case sensitive when used within formulas. If field is defined as Qty in design, using qty or QTY in formula results in an invalid formula.
- For variable declaration or manipulation, ensure not to use certain keywords that are reserved for formulating an expression.