Transforming data
- Adding a data transformation
- Configuring a data transformation
- Changing the order in which data transformations are applied
- Deleting / Editing a data transformation
It is possible to transform the columns in a data model. This allows you to:
- transform the input data: replace one value with another, fill in missing values, capitalise letters, etc.
- to add new data columns based on existing columns: short labels, codes corresponding to the column’s value, etc.
- to concatenate columns...
Adding a data transformation
To add a data transformation, you can:
- open the Data Transform tab and click the +Add button
— or —
click on the header of the column you wish to transform (in this case, Date) and then, in the context menu that appears, click on Data Transformation.
➡ The Creating the Transformation interface appears, showing a transformation marked as ‘Creating...’
Configuring a data transformation
Once the data transformation has been added, you can configure it:
- Select the column to which you wish to apply it from the Target column drop-down list .
If you accessed it via the context menu of the column header, it is already selected.
If you wish to display the result of the data transformation in another column, you can add an empty column by clicking Add column. - You can then define a transformation function:
- If you know the JavaScript function you wish to apply, you can enter it directly.
- Otherwise, you can search in the ‘Applied function’ field. This search covers existing shared functions and those available in the DigDash Metadata Service.
- Finally, if no results match your search, you can generate a function using artificial intelligence.
💡 To expand the data transformation creation/editing interface, click the button 
in the top right-hand corner.
Entering a JavaScript function
When the processing required is specific, you will need to create a script.
💡 To open a window dedicated to the JavaScript editor, click the button 
in the top right-hand corner of the editing area.
- Enter the JavaScript function for transforming the column directly into the editor.
You can use the following variables:Variable Description value Returns the current value in the column for each row. values Returns the values in the column (column position).
For example, values[2] returns the contents of the third column (the first column corresponds to position 0).col Returns the index of the column currently being processed. row Returns the index of the row currently being processed. rawValue Returns the current ‘raw’ value in the column for each row (without interpretation by DigDash); rawValues Returns the values in the column (column position) in their ‘raw’ format. namedValues Returns the values in the column (column name).
For example, namedValues["Age"] returns the contents of the "Age" column.nameRawValues Returns the values in the column (column name) in their ‘raw’ format. - Click Apply.
➡ The function is named Custom Script.
You can view the result of the transformation function directly in the Preview. - To finish, click the Close button
in the top right-hand corner.
➡ The function is added to the list of applied data transformation functions.
For example, we want to replace the value “Finance” with the value “Accounting” for the “Department” dimension in the input data.
Examples of use cases
| Requirement | Solution |
|---|---|
| Retrieve the current cell from another column | return values[INDEX_OF_COLUMN] |
| Retrieve the contents of the current cell | return value; |
| Depending on the value in a cell, I return X; otherwise, I return Y. | if(value == “Fixe”){ return “X”; }else if(value == “Mobile”){ return “Y”; } If the value is in another column but on the same row if(values[INDEX_OF_COLUMN] == “Fixe”){ return “X”; }else if(values[INDEX_OF_COLUMN] == “Mobile”){ return “Y”; } |
| Perform a process on specific rows Example: For the first 10 rows, I return “X”. From the 11th row onwards, I return “Y” | if(row <=10){ return “X”; }else{ return “Y”; } |
| Count the number of rows returned by the data source. | return 1; (or return row, then DCOUNT it) |
| Calculate the difference in days between two dates var difference = values["END_DATE_INDEX"].getTime() - values["START_DATE_INDEX"].getTime(); var diffInDays = Math.floor(difference/1000/60/60/24); difference = diffInDays*1000*60*60*24; return difference; | |
Deleting the function
To remove the current function, click the button 
.
Editing the function
Once the function has been applied, you can still edit it.
You can revert to the state of the last applied function by clicking Undo.
Searching for an existing function
You can search for a function amongst the various functions available:
- Shared functions:
- custom functions created previously and shared with a specific role or all roles.
- useful functions provided natively by DigDash
- Functions available in the DigDash Metadata Service: the selected function will then be loaded transparently as a shared function.
To do this:
- Enter the name or a brief description of the function you are looking for.
- Click on the search icon
.
➡ A list of functions matching your search will be displayed.
- If a role suits you, click on the corresponding link.
➡ The role title will be displayed.
- Click Apply to apply the function to the selected target column and view the result in the Preview.
- To finish, click the Close button
in the top right-hand corner.➡ The function is added to the list of applied data transformation functions.
Editing the translation/description
To edit the displayed values and the function’s description in different languages, click the multilingual edit button 
.
See the page Translating an object’s name for further details.
Deleting the function
To delete the current function:
- Click the Cancel button or .
Viewing and editing the script
To view the function’s script, click the button 
.
You can then edit the script in the editor.
If you edit the script, when you click Apply, you will be presented with several options:
- Update existing function in manager: the function is updated with the changes across the board.
❗This will affect all elements that call this function. - Save as a new shared function: a new function is created with the changes made to the original function.
In this case, you will be asked to enter an ID and select a Role to which access to the function is restricted, or ‘None’ to share it with all roles.
➡ The function will then be available in the list of shared functions for future use.
❗The new function will have a different ID but will retain the same display value as the original function. So remember to the display value in the tooltip or by clicking the multilingual edit button . - Apply the script on the column: the function then becomes a custom script with the corresponding options. It cannot be reused.
Generating a function using artificial intelligence
💡 For more information, you can also watch our video tutorial Data transformation via AI.
- Carry out a search as described above.
- If no function matches your search, click ‘Generate one with AI’.
Please note that your prompt must be as precise as possible to achieve the desired result.
➡ A JavaScript function is then generated and displayed.
- You can edit the script. For example, here, you can change the position of the columns to be concatenated, bearing in mind that the first column is at position 0.
- Click Apply.
➡ The function is named ‘Custom Script’.
You can view the result of the transformation function directly in the Preview. - To finish, click the Close button in the top right-hand corner.
➡ The function is added to the list of applied data transformation functions.
Deleting the function
To remove the current function:
- Click the Cancel button or .
Sharing a function
In the case of a custom script, whether created manually or generated by artificial intelligence, it is possible to save it as a shared function for future use.
To do this:
- Once the function has been defined in the ‘Create Transformation’ section, click ‘Share’.
➡ The ‘Save as predefined function’ dialogue box appears. - Enter the function ID.
- Select the Role to which access to the function is restricted, or None to share it with all roles.
- Click OK.
➡ The function is named after the ID entered and is then available in the Shared Functions Manager. - You can edit the displayed value and description of the function in different languages by clicking the multilingual edit button . See the page Translating an object’s name for further details.
Making a function parameterizable
It is possible to make a function that uses variables parameterizable so that it can be reused later with selectable columns.
- Suppose you have a function that uses variables, for example, a function that concatenates three columns:
- Click on ‘Make parameterizable’.
➡ An interface allowing you to select the columns to be concatenated is then available.
- Click ‘Share’ as described above so that you can reuse it later.
Changing the order in which data transformations are applied
By default, transformation functions are listed in the order they were created and are applied in that order.
To change the order in which functions are applied:
- Select the function you wish to move and click the
or 
button to move the function up or down the list.
Deleting / Editing a data transformation
To edit a data transformation function:
- Select the function from the list and click Edit.
➡ The Edit Transformation interface appears, allowing you to modify the function in much the same way as when you created it.
To delete a function:
- Select the function from the list and click Edit.