Map data from source to target

Map data from source to target

A mapping enables you to define the columns to upload from your original file, and map them to the required fields.

The gather360 tool also enables you to create expressions and formulas to transform the structure of data at the mapping stage. This can help you adjust the data in your source file to meet the required fields. An example of an expression might be the SPLIT function, which enables you to separate information in one column into separate columns. 

Step 1: Associate Columns with Required Fields 

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

On the left (black square) you are viewing the target fields. On the right list (red square), you are viewing the column names within your file upload which is your source data. To create mapping you need to drag source data into the box next to the target field (green square)



Alternatively, type in the target field expression box the source field you want to map enclosed in curly brackets i.e. {Policy Id}. If successful you will see a sample value from the source data appearing outside the expression box.



All target fields that are marked as mandatory must be mapped. Ensure you save your progress as you go by clicking "Save" and naming your new mapping

Step 2: Apply formulas or expressions to transform data

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

Access the expression builder by clicking on the 3 dots on the right of the expression box. This will open a pop-up titled "Expression Builder".

Here you will be presented with the source fields on the left. There is also a list of re-usable formulas that are useful for simple data manipulation.

Get started by dragging and dropping these formulas into the code area on the left. You can also drag and drop source fields into the code area on the left.

Commonly used mapping formula

Here's a sample dataset that we'll be using as an example: 

Full Name
Date
Reference Code
Address Line 1
Address Line 2
Postcode
Order Quantity
Price
Total Sales
Category
Product
John Smith 
21/11/2022
"  AS32 1234Z     "
N5, Road Avenue
Coventry, West Midlands, UK
CV1 3DK
536
17
6801
"Food"
"Cereals"

MANUAL ENTRY

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

Text

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

"Marketing"

Number

Let's say you not only need the department, but 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. 

10

Date

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()
TRIM

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

TRIM({Reference Code})

LOOKUP

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})

DATEVALUE

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")

IF

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")

CONCATENATE

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"

SPLIT

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). In case that you need the second value you will require 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]

Retrieve a value from the file name, or tab name

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]

MATHEMATICAL: Addition, Subtraction, Multiply, Divide

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}

Step 3: Testing your formula

To test your formula the code, click on the "Parse Expression" button. If your formula is constructed correctly, you will be presented with a free form area to enter in an example value for the data you wish to transform. By default this area will be filled with a sample data record from the source file, but you can also add sample values of your own.

To test the result of the formula on the sample value click "Test Expression". If successful, the returned result will appear at the bottom.

Step 4: Saving, and Re-using a mapping

Any created mappings can be saved for use again, and accessed from the mapping drop down box.

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. 



    • Related Articles

    • Reducing data preparation time

      Reducing data preparation time How to automate data feeds and reduce your data preparation time. The gather360 KPI dashboard enables users to identify bottlenecks that are increasing your data preparation time and slowing down your data supply chain. ...

    • What is a data supplier?

      https://help.gather360.io/en/articles/5854623-what-is-a-data-supplier What is a data supplier? Learn how to collaborate with data suppliers to improve your data supply chain A data supplier is an organisation or individual that you invite to ...

    • Data validation in-line editor

       https://help.gather360.io/en/articles/5104302-data-validation-in-line-editor Data validation in-line editor Errors or warnings relating to uploaded data can now be analyzed and corrected directly within the gather360 user interface. Release 2.9 ...

    • Upload data as a data consumer

      How data consumers can upload data This flow is primarily used by data consumers who do not require a supplier to upload data on their behalf. Usually, the consumer will already have access to the data that has been previously sent via email or SFTP ...

    • Data preparation time metric

      https://help.gather360.io/en/articles/5856999-data-preparation-time-metric Data preparation time metric Learn more about the 'data preparation time' metric in your workspace KPI dashboard. The data preparation metric on your Workspace Dashboard ...