Mapping tells Verodat which columns from your uploaded data correspond to the required fields in your dataset. Here's how it works:

This step creates an association between the columns in the file you have uploaded, and the ‘target fields’ required in Verodat.

This step enables you to make further adjustments to the data in your uploaded file to match the required fields.

To create a transformation rule, click on those edit icon next to the target fields to add a transformation expression to a field. A pop-up titled "Expression Builder" will appear.

This will open the code editor where you can add your function. Functions are much like an Excel Formula

Verodat enables you to manually enter data against a mapping target field. This will populate every row of your uploaded file with your manually entered data. You can manually enter a date, text, or a number.

To enter text against a specific column in your dataset, you must surround it with "quotation marks". For instance, if you are required to classify each expense by department and that field does not come in the file to be uploaded. In these cases, you could directly pass a text value (in quotes) to the expression, so every row uploaded by the marketing department will be categorized as "Marketing".

Let's say you need the department and the Cost Centre code. You could pass a number directly to the Cost Centre target field, and these could defer for each mapping. A number does not require quotation marks, so it can just be entered as 10.

If you also want to include a manual date entry against your mapping, there are a few options. To enter one specific date, you should follow the format YYYY-MM-DDT00:00:00Z. This will enter the date you added across all rows in the submission.

YYYY-MM-DDT00:00:00Z

Another option is to add in today's date, which you can do using the 'TODAY' function. By adding the function TODAY()toEpochMilli() we return the date of processing. This value will be inserted for every row in the file being uploaded

TODAY()toEpochMilli()

Below are some shortcuts you can use to concatenate data, or perform basic mathematics.

Text

Numbers

Here's a sample dataset that we'll be using as an example in the below rules:

To remove extra spaces, or retrieve part of a cell value, use the TRIM, LEFT or RIGHT formula.

TRIM({Reference Code})

If you'd like to pull a value in your existing data, you can use a LOOKUP function. This function looks for a value in a column that is in an external dataset and retrieves the value specified in the required target field. For example, in our example data above, this rule matches the category from another dataset called "Tax Rate History", to the value in the Category field to improve consistency across the system.

The format for this rule is as follows: (Dataset Name (Text), column_to_match (lowercase, underscore instead of spaces), Value to Look For (Any)"

LOOKUP_EXISTS("Tax Rate History", "tax_category", {Category})

We can also use a lookup to return a value. For example, while developing mapping rules in a dataset like the one shown above, we can retrieve values from other datasets (e.g., "Tax Rate History") to complement our data and improve consistency across the system.

The format for this rule is as follows: (Dataset Name (Text), column_to_match (lowercase, underscore instead of spaces), column_to_retrieve (lowercase, underscore instead of spaces), Value to Look For (Any)"

LOOKUP_GET("Tax Rate History", "tax_category", "rate", {Category})

This function is an easy and clean way of transforming date values that are text, into dates that can be stored in date fields in the system.

DATEVALUE({textDate}, "dd/MM/yyyy")

The IF condition enables you to add conditionality to your rules. It can be used to make fields mandatory in certain conditions or fill in blanks with a default value where data is not found, without overwriting existing values in your file. It can also be used to write data to a field based on the value in another column.

It is possible to nest IF() expressions, to create more complex rules with more than 1 or 2 variables to consider. E.g., IF(true, IF(1==2, true, false), false).

The below example creates an additional status in a new column to determine if the order is above a goal amount. If the price multiplied by the quantity is equal to or over 5000, then the status is 'Goal Achieved'. If it is not, the goal is 'Not Achieved'.

IF({price} * {Quantity} >= 5000, "Goal Achieved", "Not Achieved")

Another example is to replace " "(Space) with ""(blank) to remove unnecessary space:

IF({Postal Code}==" ","",{Postal Code})

You can concatenate multiple fields of data using Concatenate, or +.

Concatenate works with only text values, whereas + can also operate as an addition sign to perform mathematical equations, or combine numeric values with text.

CONCATENATE({Address Line 1}, ", ", {Address Line 2}, ", ", {Postcode})

{Quantity} + {Product} + " has been sold this month"

You can use split to segment text values by a common divider. For example, values that are separated by a / or a space can be split easily using this formula.

To make use of those values you will need to indicate which value from the list you need to use. In our example case below, we are splitting each section of the value in 'Address Line 2', and we are using commas as the logical separator.

We want to return only city, so we are appending [0] to indicate that we want the first value in the list (starting counting from 0). If you need the second value you will need to indicate [1]. You could also apply this logic in reverse to get the last few values (e.g., {Address Line 2}[-1] will retrieve "UK".

{Address Line 2}.split(", ")[0]

In many cases, the file name has relevant information that is worth storing in a target field.

One example could be the date on which the file was processed (e.g., "July 2023"). Combining this expression with .split(), and other functions can help to have a clean output value.

For example, if the file name is "July 2023.xlsx" we can get "July" by using the formula below. This formula is first splitting the components of the file name using a space " " to separate out each value. It is then selecting the first value by appending [0].

metadata.filename.split(" ")[0]

We can also retrieve the year using the following formula. This also splits using blank spaces, getting the second value "2023.xlsx", and follows up by splitting the result using ".xl" which returns the value "2023".

metadata.filename.split(" ")[1].split(".xl")[0]

Similar to the above many Excel users store relevant data in the Excel tabs, which could be a category, a date, a product, etc. It is possible to retrieve this value using similar logic, but replacing metadata.filename, with metadata.workSheet.

metadata.workSheet.split(" ")[0]

It is possible to add many fields together and apply mathematical equations to them like addition, subtraction, and multiplication. Simply use + for addition, - for subtraction, / for divide and * for multiplication.

You can also combine any default numbers within these sums, for example if you wish to transform a decimal to a whole number you can simply multiply by 100.

{Total Sales} - 100

{Price} * {Quantity}

{Total Sales} / {Price}

The ALLOWED_VALUE_IN_LIST function is used to validate whether a given value exists within a predefined list of allowed values.

ALLOWED_VALUE_IN_LIST requires two parameters:

ALLOWED_VALUE_IN_LIST(["Y","N"],{Direct Debit})

A filter rule enables you to selectively process rows from an uploaded file by defining specific conditions to exclude certain data. For instance, you can configure a rule to process only the rows where the "Customer ID" is not null, effectively ignoring any rows with missing or blank "Customer ID" values. This ensures that only valid or relevant records are considered during processing, optimizing the workflow and preventing errors caused by incomplete data.

To add filter click on "Add Filter Rule":

Add expression as:

{Customer ID}!=null && {Customer ID}!=''

Okay, you've crafted your data magic using formulas. Now, let's make sure it works! Follow these steps:

Any rules you've built at this stage can be saved to a mapping for use again.

Click on the saved mapping and you will see the expression box for each mapped target field being filled with the previously applied settings.

Any changes you make to this mapping will need to be saved as a new mapping, or you can overwrite and update the existing mapping.

You can also click on the "+Add Filter Rule" button to open the Expression Builder for filtering your data, and add a condition to filter out certain rows.