Data transformations allow you to conveniently modify data during the import process before any data is imported into Ardoq. Data transformations are available for any import or integration done through the Ardoq app.
Ardoq currently supports a number of different types of data transformations:
Lookup and replace
Extract value in a nested structure
Round number
Arithmetic expression
Convert to number
Replace in text
Convert to date
Convert to datetime
Join columns
Chaining Transformation
Transformations can be chained together in case you want to modify the values in a column with multiple transformations. The transformation will be applied in numeric order.
Using transformations
Create a new import and select your data
Proceed to the Configure data step in the import flow
Continue with the configuration until you reach the Configure columns step
Click the Advanced button for any column you wish to apply a transformation to
Click Add advanced configuration
Click Transform data
Select a transformation from the dropdown menu
Configure the transformation rules
Click Save changes
1 — Lookup and replace
The Lookup and replace transformation allows you to find any occurrence of one value and replace it with another value.
For example, you may have an import with a field containing the word “EN” to symbolize language. If the rest of your organization’s data assets use “English” instead, you can transform all instances of “EN” into “English” during the import process.
2 — Extract value in a nested structure (JSON)
In some cases, you may have data returned from an external source as a JSON object. When you require only a single value for mapping instead of the entire JSON object, you can extract the value you want using the JSONPath query language.
Example
The following image shows a JSON object returned from a data source:
If we want to extract only the riskname value to use in our import, we’ll need to use a JSONPath query to target the value we want specifically.
Following the root.index.attribute syntax, we can specify the riskname value with the following query:
$.items[0].riskname
Note: The value will not be previewed until you go to the Review Import step of the import process.
3 — Round number
The Round number transformation allows you to simplify unnecessarily high levels of numeric precision into something more manageable. A common example would be a value like 1.000000000001 that can be simplified to 1.00.
4 — Arithmetic Expression
The Arithmetic expression transformation allows you to modify a cell value by an integer using one of the add/subtract/multiple/divide arithmetic operations. A common example would be to multiple a value like 0.15 by 100 to express a percentage.
5 — Convert to number
The Convert to number transformation allows you to ensure that the value passed into Ardoq will be treated as a number. This is useful in certain instances where you want to avoid mismatches between European and North American number notations.
For example, a value of 1.000 would be interpreted as 1 in Ardoq, but represents 1000 in European notation. You can convert 1.000 to a number to ensure it is treated as 1000 instead of 1.
6 — Replace in text
The Replace in text transformation allows you to modify part of string instead of the entire word like in the Look up and replace transform.
For example, if you want to transform 1,000,000 into 1000000, you can replace the comma with an empty string, as shown below.
7 — Convert to date
The Convert to date transformation allows you to parse a value using datetime notation so that the value is correctly stored as a date in Ardoq. This is useful if your imported data is stored in an uncommon date format that the Ardoq system does not expect.
For example, the value 18-September-2024 can be parsed with the following format string: dd-MMMM-yyyy
You can find more details on parsing using the datetime format here.
8 — Convert to datetime
The Convert to datetime, much like the previous transformation, allows you to parse a value using datetime notation so that the value is correctly stored as a datetime in Ardoq. This is useful if your imported data is stored in an uncommon date format that the Ardoq system does not expect.
For example, the value 18/09/2024 01:40:19 can be parsed with the following format string: dd/MM/yyyy hh:mm:ss
9 — Join columns
The Join columns transformation allows you to concatenate a value from another column to your chosen column. You can also include an optional separator between the two joined values. In the current implementation, the second column is always attached to the right of the first column.
For example, if you had a column with a first name (John) and a last name (Smith), you can apply the join-column transform to attach the last name to the first name and form a full name of John Smith to be imported.
