BitCurb Crunch rule editor is the central place in the portal, where the users defined their expressions and rules types to executed the supported operations we offer – filtering and reconciliation (matching).
Without a valid formula, the rule will not be saved, therefore templates cannot be created and operations cannot be run.
For the purposes of this article we are going to use screenshots from the portal host, however we will include screenshots from Excel add-in as well and highlight the main differences.
One rule is comprised from the following components:
- Structure definition – defines the columns/structure used in the formula. This structure defines all field names and types that are passed as input to the BitCurb Crunch Engine for which we want to define a rule.
Note: The structure must only define the columns (fields) that are going to be used in the current rule expression, not all the columns (fields) that are passed as input when running the operation.
- Editor ribbon – controls allowing you to add/edit formula functions. BitCurb Rules engine supports a number of predefined rules, grouped logically. Please refer our dedicated section in the help portal to find out details about particular function or group of functions.
Function can be embedded.
- Formula body text area – actual formula, allowing to manually type in/adjust the formula body
Structure definition outlines the data structure we are going to be building the formula for. The structure rows must describe all columns used within the formula, there is no need to define the columns that are not used in the current formula and are part of the input for the crunch operations – reconciliation (matching) and filtering. Each definition row contains of the following:
– Name – the unique name of the column within the data source. Please note that the names cannot contain spaces. If your data source columns contain spaces these need to be corrected.
– Data type – the type of the data we are expecting to find in the column. This could be:
- Date – Date Time or Date value
- String – any text value
- Int32 – number, without decimal separator (point) in the range from -2,147,483,648 to +2,147,483,647
- Int64 – number, without decimal separator in the range from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807
- Boolean – TRUE OR FALSE
- Decimal – number that uses a decimal point followed by digits
– Source side – weather the column is in Side 1 ($1) or Side 2 ($2) of the formula where it will be used. In case you perform operations only against one target side, we advise to use always $1.
To create a new structure row, click on the “New row” button on top of the definition table in the BitCurb Crunch portal, or on “New structure row” link under the rule structure section in BitCurb Crunch Add-in.
Creating a new structure row in BitCurb Crunch portal will add a blank row in the structure definition table. You can then add the required information and save the new definition row:
Once defined, the structure fields can be used in the definition of the body in the body text area. They become available in the $1 Side and $2 Side dropdowns.
Editor ribbon defines all functions and operations which can be applied in the formula. Formulas are grouped into sets based on their use:
If a command parameters count is a variable number, they are identified by “…”. The dots will need to be removed from the final formula and any additional parameters can be added with a comma separator between them:
Formula body text area
The text editor for the formula body is where the formula can be defined. It is color-coded for easier identification of the different components and users can type in directly or append commands from the ribbon.
There are few shortcuts that can be found useful when working with the formula body editor:
- Double click will select the literal which has been clicked on. This can be useful for replacing/deleting the selected section.
- When typing a command in the text area directly you can press Ctrl + Space to trigger the intelligence. If there are more than one match found for the entered text, the options will be presented by a drop-down under the text. Otherwise the matching command will be placed immediately:
Validating the formula body
Once the formula body is created and structure defined, it is recommended to validate it before saving the rule. Rule cannot be saved if the formula is not valid. To do so, click on “Validate Syntax” button under the formula text editor. In the Excel Add-in the button is called “Validate”:
In case of an error, the first thing to check is the structure definition and if it corresponds to the values used in the formula body.
Rule types in BitCurb
Currently BitCurb Crunch engine supports two types of rules – Matching (Reconciliation) rules and Filtering rules.
The reconciliation rules are used to perform data reconciliation between data sources based on your rules definition and support “One To One” and “One To Many” matching algorithms. Please, refer our how-to article for details about how reconciliation rules might be used with BitCurb Crunch Engine.
Filter rules are used to refine your data based on the rules expressions you define. Please, refer our how-to article for details about how filtering rules might be used with BitCurb Crunch Engine.
Matching (Reconciliation) rule
If you try to create a new rule, by default the rule is marked as Matching rule. The type is controlled through the checkbox “Is match rule?” and when the checkbox is ticked, that means the rule is defined as matching rule.
Once your rules are defined they can be assigned to a template, which gives higher level of reuse.
Every assigned rule in a template must be the same type – matching or filtering. Mixing different types in a template is not supported.
- Scope Type
This value defines the type of crunching algorithm to be used – 1To1 or 1ToMany. When “One To Many” is used, the Direction field value may make difference in the results you are getting, since it will define which is the side to be used as One and which is the side to be defined as Many.
This value represents the direction the crunching algorithm to use when comparing the 2 sets of data – $1 To $2 or $2 To $1.
The definition of the $1 side and $2 side may come through your API request definition, when API is used or through the Excel table formatting definitions, which are identified when the template is defined.
In case your rules are consumed through Excel BitCurb Crunch Add-In, you must define a template and assign your rules to it. In the template’s definition you have to provide the names of your custom tables formatting, which define Side 1 ($1) and Side 2 ($2).
Rules that are designed to be consumed through API may be invoked without explicitly been assigned to a template.
Variance checkbox defines if the matching rule must be executed based on given difference in the Crunch field. You can define an expression for the difference you would like to apply when matching data sets with variance.
- Crunch Field
This is the field in the data set item used for the crunch operation. This is usually the amount value based on which you would like to perform reconciliation. The field must be defined in the Rule Structure Definition, before appearing in the dropdown.
To define Filter rule in BitCurb portal, you only need to unselect the “Is match rule?” checkbox when New Rule form is loaded.