General Info

BitCurb Crunch rule editor is the central place in the portal, where the users define their expressions and rules types to execute the Crunch 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 section defines all field names and types that participate in formula expressions or as Crunch field.

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 – a set of 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 in other functions providing you a powerful way to define complex correlations.

• Formula body text area – actual formula, allowing to manually type in/adjust the formula body

Structure Definition

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 their names 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 – this choice defines whether 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 you to use always $1.

To create a new structure row, click on the “New row” button on the bottom 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 list. You can then add the required information and save the new definition row:

Creating a new structure row in BitCurb Excel Add-in will open a structure row form to fill in:

Field names in the structure list must be unique within the scope of the side they are created, i.e. $1 and $2. However the same field names can exist in the different sides of the formula.
Once defined, the structure fields can be used in the definition of the body in the body text area. They become available in the drop-downs for $1 Side and $2 Side .

Editor ribbon

Editor ribbon defines all functions and operations which can be applied in the formula. Formulas are grouped into sets based on their usage:

Clicking on a button will expand and show the commands under it:

Certain groups can be pinned to the ribbon, these are Operations, Atom and Simple commands sets:

To pin a group on the ribbon click on “Pin ribbon” command which can be found on the first position of the set:

Due to the limited space, the ribbon buttons in the Excel Add-in and in mobile view of the portal are only presented by their icons:

The first two buttons in the ribbon are populated dynamically by the structure definition rows:

Selecting a command from the ribbon will add the corresponding command in the formula body editor with the placeholders of the parameters (if any) required by the command.

When adding a command with optional parameters, the parameters which are not required are in square brackets:

Please, remove the brackets if you are using the optional parameter.
If a command can have multiple parameters and their number is not fixed, these parameters are identified by “…”. The dots will need to be removed from the formula expressions 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 the place, where the formula expression 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 part, 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 IntelliSense feature. If there are more than one match found for the entered text, the options will be presented by a drop-down displayed under the text. Otherwise the matching command will be placed immediately:

Note: Please, be cautious if you are copy-pasting formulas from our user guides and help documents, Sometimes the string (text) could be pasted not using the correct double quotes.

Validating the formula body

Once the formula body is created and structure defined, instantly you will see a message “Ready” in the lower left corner of the formula body text area for a valid formula. If the formula is not valid “EXPRESSION IS NOT VALID” message will appear. Rule cannot be saved if the formula is not valid. In the Excel Add-in to validate before saving the rule click on “Validate” button:

 

If the formula is valid you will get a message saying: “Your rule’s body syntax is correct.”
In case of an error, the you should check the structure definition list and the names used in your formula body.

Rule types in BitCurb

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 Filtering rule. The type is controlled through the dropdown Scope Type.
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.

The following attributes are only valid for Matching rule:

• 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.

• Direction

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 Crunch template is created.

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 created to be consumed through the Crunch API, may be invoked without explicitly been assigned to a template.

• Variance Value

Variance value field defines if the matching rule must be executed based on given difference in the “Crunch” field values in your data. You can define an expression for the difference you would like to apply. when matching data sets with a variance.

• Crunch Field

This is the field in your data used for the crunch operation. This is usually the amount value, based on which you would like to perform the reconciliation operation. The field must be defined in the Rule Structure Definition list, before appearing in the drop-down.

Filter rules

To define Filter rule in BitCurb portal, you only need to click on the NEW button in the Crunch/Rules menu.By default the rule’s type is Filter.