Building Rules

Building Rules

What are rules?

The gather360 rules engine offers a range of ways to apply data governance and business rules to prepare and transform data into a report-ready format.

In this article, you'll find information on:

1. Rule Types

Rule Types

There are several different types of rules available:

Filter Rules

These rules enable data submissions to be filtered before upload to eliminate unnecessary rows.

Mapping Rules

Mapping rules make it easy to match the fields of a data upload to the target state in a gather360 dataset. gather360 enables the creation of complex mappings for multiple fields, featuring the option to use mathematical expressions and concatenation options to map data to the target state.

In addition, the gather360 interface enables the creation of multiple pre-configured mappings per data source.

Validation Rules

Validation rules govern and cleanse submitted data. The system checks data uploads against requirements and prompts data suppliers to resolve errors before submitting data to ensure that all submitted data meets quality requirements.

Validation rules can vary in complexity and are custom configurable within the gather360 interface. These rules always return a 'True' or 'False' status. True means the data has been validated and meets requirements. False means the data has been validated but does not meet requirements.

There are three categories of Validation rules:

  • Basic: When adding fields in gather360, we specify the format (i.e. string, integer, number or date) and whether it is mandatory or optional. The system automatically generates rules to test all submitted data against this field criteria.

  • Warning: Data that returns a 'False' status will be flagged to the uploader but won't prevent submission. The error can be optionally resolved and re-tested. If the error is not resolved, the data submission will be flagged and made available for analysis in the data layer.

  • Critical Error: Data that returns a 'False' status will be prevented from submission. The error must be resolved and the validation test repeated before data will be accepted.

Transformation Rule

Transformation rules convert data fields from one format to another. Transformation rules consume input data or available enrichment data to create a new data point based on configured requirements.

What type of rules can gather360 create?

gather360's flexible rule engine can be easily configured to support most business data rules.

The system uses 'logical operators' to enable complex multi-level rule building in a formula-like environment. The system utilises mathematical expressions and boolean logic to convert business logic rules into rule functions.

Where there is an ongoing need for complex rules to be utilised across the business, gather360 enables the creation of customised functional operators that can be saved in a simplified format for re-use.

Rules can be triggered from or include any data point that has been uploaded into the gather360 system and can incorporate data from custom reference tables or enrichment data. The scope of data that can be used in rule building is shown below:

Rules can be triggered from or include data from:

  • A field - The value stored in the field.

  • A row - The value stored in the field with one or more values within that row

  • A data file - The values stored in a field/row with any other value or rows within the data file

  • Other data sets - The value stored in field/row with any other value stored in other datasets previously uploaded

  • Other reference sources - The value stored in a field/row with any other reference data store made available to the platform for lookup and processing.

Rule Examples

Please see a selection of rule examples below:

  1. Lookup rules: checking fields against a defined reference table list (e.g. currency, country code, ZIP code)

  2. Conditional rules: specifying an allowed range for values (e.g. date range, price range, time range)

  3. Chronological /Combinational rules: creating a required combination or sequence that data must follow (e.g. Column A must be within 30 days of Column B)

  4. Computational rules: mathematical expressions (e.g. sum of orders must equal total order amount)

  5. Transformation rules: formulae to convert the format of data from A to B (e.g. exchange USD to GBP or split DD-MM-YYYY to DD-MM)

Customised Rules

The gather360 rules engine is very flexible, so there are very few limitations on rule building. Where complex additional functional rules are required, the g360 technical support team can provide these via our helpdesk.

Pre-built rules

A number of rules are available as pre-built 'drag and drop' functions, so they are easier to re-use across multiple datasets. Many of these functions mirror to what's available in Excel and other spreadsheet tools. A description of some of our most popular functions can be found below. 

FunctionParametersBusiness DescriptionComparison to Excel
TRIM()TRIM(String)Trim all extra spaces from text.TRIM
RTRIM()RTRIM(String)Trim extra spaces from the right side of text only.TRIM
LTRIM()LTRIM(String)Trim extra spaces from the left side of text only.TRIM
LEFT()LEFT(String,int)Grab text from the left of a string of textLEFT
RIGHT()RIGHT(String,int)Grab text from the right of a string of textRIGHT
MID()MID(String,int,int)Extracts a given number of characters from the middle of a supplied text string. For example, =MID("Apple",2,3) returns "ppl".MID
LOWER()LOWER(String)Converts a text string to all lowercase letters. Numbers, punctuation, and spaces are not affected.LOWER
UPPER()UPPER(String)Converts a text string to all uppercase letters. Numbers, punctuation, and spaces are not affected.UPPER
FIND()FIND(String,String)Returns the position (as a number) of a section of text, by searching inside the full cell. E.g. If the target is to find A and the data cell contains 'Apple', then the result will be '1'. If the target is to find 'e' the result will be '5'. When the text is not found, FIND returns a #VALUE error. This can also work with numbers so long as the value is a string. You can also set a start position that controls where FIND should begin looking. FIND is case sensitive.FIND
SUBSTITUTE()SUBSTITUTE(String,String,String)Replaces text in a given string by matching. E.g. if a cell contains '01345-86839' and the function removes '-', the returned value will '0134586839'SUBSTITUTE
IF()IF(def,def,def)Runs a logical test and returns one value for a TRUE result, and another for a FALSE result. More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR to extend the logical test. You can set a value to return if true, and a value to return if false.IF
MATCH_NUMBER()MATCH_NUMBER(String,int)A function used to locate the position within a given text.MATCH
DATECONVERT()DATECONVERT(String,String)Converts a date represented as a text string into a valid date. Returns a date value from a given text with date format specified i.e. "yyyy-mm-dd".
Amends a date by adding a DAY, MONTH, or YEAR section. E.g. Add '2023' to a date. Must be applied using Java ChronoUnits.
DATEDIF()DATEDIF(Instant,Instant,ChronoUnit)Returns the difference between two date values in the specified unit (i.e. YEARS, MONTHS, DAYS)DATEDIF
ALLOWED_VALUE_IN_LIST()ALLOWED_VALUE_IN_LIST(def,def)Returns TRUE or FALSE if the value contains a match to one or many listed values in this rule. This is an alternative to a lookup rule which looks for a match against values in another dataset.Can be completed in Excel using Conditional Formatting
FORMAT_CHECK()FORMAT_CHECK(String,String)Returns true if the value matches a specific format, as defined in the regular expression regex. If there is not a match, returns false.
DUPLICATE_CHECK(String...)Detects duplicates for field(s). Returns false when any row contain duplicates within the checked field.
LOOKUP_EXISTS()LOOKUP_EXISTS(String,String,def)Returns TRUE or FALSE if the value contains a match to a corresponding value in a one-column row in a reference table. E.g. Checking if a ID value matches an ID in the Customer dataset. This is an alternative to a list rule which looks for a match against values listed in that rule.VLOOKUP, HLOOKUP
LOOKUP_GET()LOOKUP_GET(String,String,String,def)Retrieve a value from the same position in another dataset, by identifying a matching value, and retrieving values from the adjacent columns based on the position specified in the rule. E.g. If a ID value matches an ID in the Customer dataset return the value that is two columns to the right in the lookup table.VLOOKUP, HLOOKUP
UK_POSTCODE_EXISTS()UK_POSTCODE_EXISTS(String)Returns TRUE or FALSE if the value contains a match to reference table of approved UK postcodes.VLOOKUP, HLOOKUP

If there's a function missing that you'd like our team to add, please message us or add a ticket in the Help Centre and we'd be happy to help. 

    • Related Articles

    • Rules coverage metric Rules coverage metric Learn more about the 'Rules coverage' metric in your workspace KPI dashboard. The Rules coverage KPI is a calculated score that indicates the overall status of ...
    • Creating a dataset

      How to build your first dataset in gather360 To enable gather360 to prepare and request data for your organisation, you must define the target state for the data that you need. To do this, you must create a dataset. A dataset specifies the target ...
    • Commonly used mapping functions

      Building mappings in gather360 is relatively simple. Here is a list of the most common mapping functions: Strings & - To add two fields together & " " & to add two fields together with a space & "," & to add two fields together with a comma Numbers + ...
    • What is a dataset?

      An overview of the core elements within a dataset A gather360 dataset is a core entity that users can customise within the platform. It contains detailed settings and requirements for your specific data need. These settings define three core things ...
    • gather360 Introduction

      Get to know the main features and functionality of the gather360 platform. ​ Workspace A workspace is the name of your organisation's main dashboard. This is where your team can collaborate to manage data sets, data suppliers, data schedules and data ...