Skip to main content

Aperture Data Studio user guide

Overview

We strongly recommend using Chrome to access the application.

If you have any issues or questions, try troubleshooting or contact support.

Find out about main features and benefits of Aperture Data Studio.

Aperture Data Studio allows you to:

  • Get started quickly with an easy installation and an intuitive browser-based interface
  • Save time and money by creating operational data quality workflows
  • Improve transparency with audit reports
  • Increase efficiency of your data migrations
  • Standardise and improve quality of your data with powerful data transformations
  • Encourage collaboration with reusable workflows

Quick start

Once you've installed the application, we recommend that you change your password before uploading your files. Click on your username and select Change password.

To get started, see an overview of the functionality or try out use cases.

Functionality overview

We recommend that you change your password before uploading your files. Click on your username and select Change password.

  1. Upload your data using the Data Explorer. You can either:
    • upload your source files: right-click on My files > Upload file or
    • connect to your database using a JDBC driver: Data sources Click here to create a new data source.

  2. Preview, configure and tag your data.

    Preview the first 1,000 rows and check that your data is in order. If required, make changes: right-click on your file/data source and select Preview and configure.
    You can move, rename and exclude column headings and change character sets/delimiters/quotation marks. Click Apply in the top-right corner when you’re happy with the changes. The file will now appear as 'Configured'.

  3. Load and explore your data.
    Right-click on it and select View data. The previewed file will now appear as Loaded. To profile the data once the file is loaded, click View Profile/Outliers. It will appear as Profiled.

    Larger files might take a while to load. A percentage progress bar will be shown over the file name in the explorer view (on the left-hand side).

    You can now understand your data: filter, sort, view the outliers and discover inconsistencies. Ensure the loading has completed before you start manipulating the data.

  4. Create workflows.
    In the Workflow Designer, click Create a new workflow, name it and click Submit. Add your loaded file(s) and open the Workflow steps tab to manipulate your data as required in order to:
    • create a new and improved version of your data,
    • structure and prepare it for migration,
    • validate against user defined rules,
    • find matching/duplicated values,
    • cleanse records and more.

  5. Report/export your data.
    In the Workflow Designer, you can create a .pdf report for each workflow: click Report in the top menu. 
    You can also save the workflow output as a .csv, .txt, .xls, .xml, .dat, or a .json file by clicking Execute in the top menu. To re-use a workflow, save it as a .wfl file by clicking Export in the top menu.

Data Explorer

Data Explorer allows you to manage your data sources, prepare and configure your data.

Sources

This menu lists all your data sources: both raw files and database connections.

My files contains files uploaded by you only; Data sources lists all the available connected/configured database connections.

To upload/connect to your data, do one of the following:

  • drag and drop files into the window or
  • go to My files > Click here to upload a file or
  • click upload in the top menu or
  • connect to your database using a JDBC driver: Data sources > Click here to create a new data source.

The supported file formats are:

  • .csv
  • .txt
  • .xls
  • .xlsx
  • .sas7dbat
  • .psv
  • .dat 

Note that .dat files have to be uploaded together with the associated metadata files such as .cob or .ddl.

The supported database drivers/services are:

You can create JDBC connections for the following databases:

  • Amazon Redshift
  • Apache Hive which supports:
    • Apache Hadoop Hive
    • HortonWorks
    • Cloudera CDH Hive
    • Amazon EMR Hive
    • IBM BigInsights Hive
    • MapR Hive
    • Pivotal HD Hive
  • DB2
  • Greenplum which supports:
    • Pivotal Greenplum
    • Pivotal HAWQ
  • IBM Informix
  • MongoDB
  • MySQL
  • Oracle Database
  • Oracle Service Cloud
  • PostgreSQL
  • Progress OpenEdge
  • Salesforce which supports:
    • Salesforce.com
    • Veeva CRM
    • Force.com Applications
    • Financial Force
  • Spark SQL
  • Microsoft SQL which supports:
    • Microsoft SQL Server
    • Microsoft SQL Azure
  • SAP Sybase

Find out how to add a custom JDBC driver

Restricting access

For each data source, the administrator can specify individual login credentials for each user/user group to ensure that restricted data is not accessible to anyone without the appropriate permissions.

To create and assign access credentials:

  1. In Data Explorer, click on the required data source (e.g. Salesforce).
  2. Click Credentials and then Add credentials.
  3. In the Add Credential dialog, specify the name for this credential. We recommend using a descriptive name as this will be used when assigning the credential to users/user groups.
  4. Enter the username and a password.
  5. Click Edit to specify the table(s) that user(s) with this credential will have access to. 
  6. Click Apply to save changes.
  7. Go back to the previous screen - click Credentials.
  8. To apply the newly created credential to individual users, click Users; to apply to a group of users, click Teams. 
  9. Right-click on the required user/team and click Add access.
  10. Specify the credential and click Apply.

To remove access from the user/team, simply right-click on them and select Remove access.

Preview and configure

To ensure your data has been interpreted correctly and is displayed as expected, you can preview it: right-click on your source data and select Preview and configure. A preview of the first 1,000 rows will be shown. 

We strongly recommend that you preview your data before loading it. This ensures that the data we cache is as expected.

You can move, rename and exclude column headings and change character sets/delimiters/quotation marks. 

To configure how the data should be interpreted, use the Data tab.

Depending on the file type, you can specify various settings. For example, for .csv files:

  • Character set: the character set for the data.
  • Language and region: the formatting region (e.g. whether to use UK or US format for dates).
  • End of line delimiter: the sequence of characters denoting the end of the line.
  • Delimiter: the delimiter to be used for separating fields in this file.
  • Quote: the delimiter to be used when loading quoted values from delimited files.
  • Headings on first line: include or exclude column headings in the first row.

The Headings tab allows you to manipulate the column headings: you can rename, re-order, remove and exclude them. You can also tag data.

Alternatively, modify columns by right-clicking on the column header, selecting the required option and clicking Apply in the top-right corner to save your changes. 

Standardize data

You can standardize your data easily before loading it into Data Studio: right-click on the column header and select Edit.

You can now apply one or more standardisation options: 

    • Auto-parse integers: convert numeric values with no fractional part to integers. We don’t recommend disabling this option if the column values are integer numbers.
    • Auto-parse decimals: convert numeric values with fractional parts to decimals. We don’t recommend disabling this option if the column values are decimal numbers.
    • Auto-parse dates: parse dates based on the language for the file.
    • Auto-parse scientific numbers: convert values in scientific notation (e.g. ‘1E47’) into numbers.
    • Treat blanks as null: we recommend to have this option enabled to treat all blank values in the column as empty/null.
    • Remove leading blanks: we recommend to have this option enabled to remove the leading white space from the column values.
    • Remove trailing blanks: remove the trailing white space from the column values.

      Note that if the Treat blanks as null option is turned off, and both Remove leading blanks and Remove trailing blanks are on, Data Studio will ensure that at least a single space is retained for entirely blank values.

If you want to keep the null and blank values separate, we recommend that you turn off Remove leading blanks and Remove trailing blanks. Otherwise, Data Studio will not load values with varying spaces and will not treat them as different values.

By default, all of the standardization options above are enabled (except for Auto-parse scientific numbers). To change the enabled/disabled options for all files:

  1. Go to Configuration > All server settings.
  2. Find Default standardisation rules.
  3. Enable/disable options as required and click Apply

In preview, you can only move headings, not data. 

To rearrange columns, you can simply drag and drop the column headings. Click Apply in the top-right corner when you’re happy with the changes. The file will now appear as 'Configured'.

To revert all the changes made to your data:

  • click Cancel in the top menu to remove changes made since the last configuration or
  • right-click on the file and select Reset configuration when not previewing the file to completely reset the configuration. 

Data tagging

Data Studio can use machine learning algorithms to automatically tag columns. Data tags allow you to enrich source data by specifying additional information about what each column contains. These are then used to improve the user experience by providing sensible defaults and suggestions. Tags are used in various workflow steps such as Find duplicates and Validate addresses.

Note that if you upload a file with the same name, the tags will be retained.

Data tags appear next to the column name, so if you're not very familiar with your data, they provide an overview at a glance.

There are two types of data tags:

  • System defined (cannot be modified, used to drive system behavior)
  • User defined


Tags in Data Studio are hierarchical: a parent tag (e.g. address) can have multiple child ones (e.g. street, city). 

While you can't modify system defined tags, you can create a child tag (e.g. PO box) and assign it to the system one (e.g. Address). Once created, it will appear in the User defined list under that system parent tag.

To create new and manage existing tags, go to Glossary > Data tags. To add a new one, click Create new data tag.

Note that Consumer users will not be able to access Glossary. Find out about user roles.

The best way to manually tag data is before it has been loaded, in preview:

  1. Go to Data Explorer.
  2. Right-click on the required file and select Preview and configure.
  3. Open the Headings tab to see all your column headings.
  4. Click Multi select and choose one or more headings of the columns you want to tag (e.g. City, Country).
  5. Right-click and select Tag columns.
  6. In the Tag column dialog, click Edit.
  7. Apply the necessary tag(s) (e.g. Address) and click away for changes to be saved.
    The Selected tab will show all the tags applied to this column.
  8. Click Tag to save changes.


If you tag columns in a workflow step, the tags will be applied to that column in all the subsequent steps for that workflow only. 

Auto tagging

Columns can be automatically tagged by allowing the system to recognize the type and meaning of data from its trained knowledge base.

For example, Data Studio can automatically detect columns containing names, addresses, and other customer-related data within a file. You can also easily train the system to recognize types of data that are specific to your organization (or not yet included in Data Studio’s knowledge base).

One of the benefits of having your data tagged is to allow the Workflow Designer to apply intelligent defaults in your workflow steps, significantly speeding up workflow creation.

Automated tagging is done by processing the data as it's loaded into the system. The processing is performed one column at a time by comparing the patterns of its values with known patterns (fingerprints). This comparison results in a similarity score on a scale of 0-100, and there is a configurable threshold to determine how similar the data must be to the fingerprint in order for a tag to be applied automatically.

For some tags, you may want the threshold to be high for a positive result, particularly where the values have similarity to fingerprints for other tags. For others, where the data is typically unique to that tag, you may prefer to set the threshold lower. For example, columns containing country names tend to have a very high similarity score because there's a relatively small number of countries in the world. Conversely, columns containing surnames tend to have a lower similarity score due to the much larger number of surnames in most datasets. Therefore, it's appropriate to specify a higher similarity threshold for a country and lower threshold for the surname column.

If you find that Data Studio is making mistakes when automatically tagging data, it's likely to be caused by a threshold that's set at the wrong level for the type of data you're dealing with.

Both system and user defined tags can be found in Glossary > Data tags.

Note that Consumer users will not be able to access Glossary. Find out about user roles.

To adjust the threshold, right click on the tag and select Edit to adjust this to be between 1 and 100. You can also Delete user training data to remove all the fingerprint files associated with the tag.

You can also exclude the tag from auto tagging by right-clicking on it and selecting Exclude from auto data tagging.

To enable auto tagging:

  1. Go to Data Explorer.
  2. Right-click on your data file and select Preview and configure.
  3. In the Data tab, select Auto tag data on next load.
    Enabling this will force the data to be re-read and tags automatically applied to the columns when it's next viewed. Once viewed, this option will be automatically set back to disabled.
  4. Click Apply to save changes.

 

If you have manually applied tags to your data, you have the option to train the system so that when it receives similar data in the future, it can automatically apply the same tags. To train Data Studio’s data tagger:

  1. Go to Data Explorer.
  2. Right-click on your data file and select Preview and configure.
  3. Open the Headings tab.
  4. Right-click on the column you want to use and select Tag column.
  5. Open the Data tab and enable the Train auto tagging on next load.
    Enabling this option means that the next time you view the data, it will be re-read from the source and used to train the system. Once viewed, this option will be automatically set back to disabled.
  6. Click Apply to save changes. When the process is completed, the new fingerprint files will be created and the rejection threshold might need to be adjusted.

You can also specify the Number Of Rows Used As Data Tagging Training Data – the number of rows that will be included in generating fingerprint files: go to Configuration > Loading.

Note that only administrators will be able to see this area. Find out about user roles.

View data

View data and transform it as required. 

Changes made when viewing files in Data Explorer are temporary and will not persist. However, the operations you make can be saved as a workflow and the data changes can be saved to a file.

Once the data is loaded you can: 

  • Configure and transform your data.
  • Save as workflow: saves your transformation/configuration as a workflow in 'My workflows' list in the Workflow Designer.
  • Download as .CSV: saves your transformed data as a .csv file in your browser’s default download location.

Dependency/Key analysis

Dependencies are normally used for table normalisation – a process of decomposing a table into smaller tables in terms of attribute sets.

Keys are used to identify records in a table and are therefore crucial when joining tables in cross-domain queries. The analysis of keys depends on the results of dependency analysis.

Once the table is loaded, right-click on it and select Dependency analysis

This option will be disabled if the table hasn’t already been profiled.

Using the Analyse dependencies dialog, specify the settings for the analysis such as the maximum number of columns that will be considered as a multi-column key and the minimum correlation percentage to be considered as dependency.

The analysis result will be shown under the table name, following the Configured and Loaded status.

To see the generated results, right-click on the table and select Dependencies or Keys.

 

Workflow Designer

Transform, manipulate, analyze and export your data with re-usable graphical workflows. 

If you have issues or questions with workflows, try the troubleshooting.

Available sources

This menu lists all your data sources both raw files and database connections.

My files contains files available to you only; Data sources lists all the available database connections.

Workflow steps

Transforming your data is easy with Aperture Data Studio. Visual workflows are built using a variety of drag-and-drop steps.

Some steps (e.g. Find duplicates or Validate addresses) will have to be configured and licensed before you can use them. Find out about licensing.

When creating/modifying workflows, the top menu allows you to:

  • create a .pdf report on each workflow by clicking Report,
  • re-use a workflow by saving it as a .wfl file: click Export,
  • save the workflow output as a .csv, .txt, .xls, .xlsx, .xml, .dat or .json file by clicking Execute (single output only),
  • auto-arrange the workflow steps by clicking Arrange,
  • change the cursor to be able to select multiple steps by clicking Select. To switch back, click Move.

By default, the auto-connect for the workflow steps is enabled, allowing two steps with input/output nodes to be automatically linked when moved close to each other. You can disable this by clicking Disable snap; click Enable snap to turn the auto-connect back on.

To import one or more workflows (.wfl), click  in the top menu.

Once added to the workspace, each workflow step can be renamed: double-click on the title of the step and enter the required name.

Analyze trends step

This step allows you to see data quality trends over time. 

It works best when combined with the Use snapshot range step, using snapshots taken from the Validate step (and connected to the Results for analysis node). 

Note that connecting from the Results for analysis node will mean that the output columns are automatically tagged for trend analysis. 

In the step dialog, specify the following:

  • Category - the column that determines the version of results (e.g. execution time)
  • Series - the column that determines the data series (e.g. rule name)
  • Metric - the column containing the names of the metrics (e.g. processed rows)
  • Value - the column containing the values for the metrics (e.g. 90)
  • Metric (for display) - which metric (e.g. passed rows) you wish to view across each Category and Series. Options will be populated from the Metric column. This affects both the data and chart outputs.

There are two options for displaying the trend results:

  • Show data - view as a grid with a visual indication (red or green triangle) for each value of the selected metric
  • Show chart - creates a line graph showing changes over time of the selected metric, for each series

You can also use this step to create regular reports, providing insight into the quality of your data.

Branch step

The branch step allows you to duplicate data from one input so that it can be used for further workflow steps.

The rows from the source data will be duplicated (branched) into two outputs. To create more than two duplicates of the data, you can link several branch steps.

Click Show data in the step dialog to view the results.

Chart step

The chart step allows you to create interactive charts of your data at any point in your workflow.

Including this step within the workflow will not affect the data connected to it. Connect this step to one input to create various types of charts. Click Bar chart (2D) in the step dialog to change the type of chart.

To add more columns to the chart (up to 10), click Add data series column. To further customize the chart, click Additional options.

Chart typeThe type of chart you wish to draw (e.g. bar, line, pie).
Data label column The column for labeling the x-axis. For example, if your data is split by year, use this setting to label the bars with their respective year.
Split series Specify whether to display all the data series on the same chart or split them up.
Title The title of your graph, displayed at the top. 
Description The description of your graph, displayed under the title. 
X-axis label Labels the X-axis (at the bottom). 
Y-axis label Labels the Y-axis (on the left). 
Show legend Specify whether to display the legend at the bottom. 
Show trendline Specify whether to calculate and draw a trendline on the graph - a straight line of the best fit for your data. 
Show X/Y zero line Specify whether to draw a line at the origin for the axis (useful if you have negative data). 
Logarithmic X/Y axis Specify whether to convert the selected axis to a logarithmic scale. 
Show horizontal/ vertical grid lines Specify whether to draw grid lines in the respective axis. 
Stacked? Specify whether to stack your data on top of each other. 
Show section labels (Pie chart only) Specify whether to show or hide the labels on segments. 

Click Show chart in the step dialog to view your data as an interactive chart. Click View data to also show the data alongside the chart.

The chart will be based on the top 1,000 rows.

Export step

The export step allows you to export workflow's output data.

Each workflow can contain multiple export steps, so you can export data at any point. Each export step can be configured individually.

Click Settings in the step dialog to specify export file type (.csv, .txt, .xls or .xlsx, .xml, .dat or .json) and location (server’s export directory or data source).

Enable Overwrite server file option to allow the file to be exported more than once (the latest copy will replace the previous one). If this option is disabled, you will get an error when trying to export to the same file name more than once.

To execute workflows with multiple outputs, the export settings defined in each export step have to be used.


Exporting to a DBMS

When the Workflow output type is DBMS, use Mode to determine how the data will be inserted into the target system:

  • Insert - rows are inserted into the table. The equivalent SQL statement is:
    “INSERT INTO tablename (column1, …) VALUES (value1, …)”
  • Update - rows are updated. The mapping dialog will show which source columns are mapped to which target columns and which ones have been specified as keys. You can change the mapping and keys at this stage. The keys referred to are those columns that will appear in the equivalent SQL statement’s WHERE clause:
    “UPDATE tablename SET (column1=value1, …) WHERE (columnName=value, … )”
  • Delete - rows are deleted. The keys are the column names used in the WHERE clause:
    “DELETE FROM tablename WHERE columnName=value, … “ 
  • Insert or update (if insert fails) - if a row insert fails (there is a key collision), then the statement will be retried as an update.
  • Update or insert (if update fails) - if the update fails (no record found with the given key), then the statement will be retried as an insert.
  • Create table - selecting this option will automatically create a table in the database. You will have to specify a name for the new table which will be validated against the data source. 

Filter step

The export step allows you to filter data using a variety of functions.

See Function editor for more details.

Group step

Grouping allows you to group values from one input into one or more columns and aggregate the remaining columns, if required.

Click Grouping in the step dialog then drag and drop the required columns up, into the Grouped list. Remove columns from this list by simply dragging them down.

Click  to apply the changes and Show data in the step dialog to view the results.

You also configure grouped columns and use aggregate functions on the grouped values when in Show data view: right-click on the grouped column heading and select Add aggregate. In the dialog that appears, specify the name/description for the new column, the source column to be used for aggregation and the required function.

For some aggregation functions, you can choose to only process unique values by enabling the Distinct option.

Join step

The join step allows you to join two inputs into one based on a column from each input.

To specify the columns you want to join with, click Undefined column and select the required one. Do this for the second data source.

The type of join can be configured by clicking on the Venn diagram. There are four types of join: left, right, inner or full.

Use the All matches menus to specify different combinations of join. The top left menu is for the top join table and the bottom right one for the second join table.

  • All matches - perform a standard join
  • Singletons only - will return the first match of a 1 to many match AND any matches that occur only once (this is a normal relational join with duplicates removed)
  • Duplicates onlywill return all the matches of things that are 1 to many only (this is a normal relational join excluding things that match only once)

In the join dialog, click Show data to view the results of the join. Specify which values from the joined columns to use in any following steps: all the matches, only duplicates or only singletons.

Click  to see the suggested joins. Only exact matches from the two sources will be suggested. Click on the suggested join to apply it. 

Multi view step

The multi view step is a very useful step for viewing your data side by side (horizontally or vertically).

You can link this step to 2-3 data sources to view and compare the data. By default, the horizontal view will be selected. To change to the vertical view, click Horizontal split.

Click Show view to display the results. For more complex views, you can also link this step to another multi-view step.

Only the top input will be passed on to a subsequent non-multi view step.

Profile step

This step allows you to profile data at any point in a workflow. We examine each value and identify various attributes (such as type, min/max values, count) and determine its uniqueness and completeness.

Click on Profiled columns to select the column(s) you want to profile. In the Profiled columns list on the left, select the required ones and click . To see the results, click Show data in the step dialog. 

Note that results might take some time to load.

You can now export or take a snapshot of your profiled data to track data uniqueness over time. 

Sample step

This step allows you to create a sample of the data the step is connected to. You can specify the following sampling settings:

  • Start at row -  the row number from which to start loading
  • Sample one row in every - specify the frequency of rows to sample
  • Sample randomly - enable to create a random sample
  • Limit rows - enable to specify a Row limit that will be used for the sample

Script (JavaScript) step

To use this step, you have to have at least one JavaScript available in the script library: Glossary > Scripts.

This will allow you to use the script as a workflow step to transform your data.

Click Show data in the step dialog to view the results. The output can then be used as input to any further step(s).

Script (R) step

To use this step, you have to have at least one R script available in the script library: Glossary > Scripts.

This will allow you to use the script as a workflow step to transform your data.

Click Show data in the step dialog to view the results. 

Sort step

This step allows you to sort data in ascending/descending order.

Click Sorting in the step, select the required columns and click  GreenTick.png to save changes.

To change the sort order, click on the column in the Sorted list.

Snapshot steps

A snapshot is a versioned copy of data taken at a certain point in time (and saved internally in Data Studio as a table with any transformations). Each snapshot is automatically assigned a unique name which you can modify.

Once created, a snapshot will appear in the list of Available data sources and can be used in any workflow. 

Take snapshot step
This step will create a snapshot (once the workflow is executed). Each time you execute a workflow with this step, a new version of the snapshot will be created.

Use latest snapshot step
This step allows you to use the latest available snapshot. First, choose the workflow it belongs to then select the required snapshot.

Use snapshot range step
This step allows you to use several snapshots at a time (it's the union of all the snapshots in the range). If your data changes over time, you can compare the current version to the previously created snapshots. To choose the required snapshot range, find the workflow it belongs to then select the snapshot range.

Click Show data to see the details of all your snapshots in one table.

Find out how to configure an ODBC connection.

Splice step

Splicing allows you to combine two inputs into one by presenting the values next to each other.

Data in the columns is not combined or merged - the duplicated values are renamed with an appended numeric value. The output – combined values from two sources – can then be used as input to any further step(s).

Click Show data in the step dialog to view the results.

Split step

The split step allows you to apply a true/false filter to one input and split the resulting data into two outputs based on these values.

To create a filter, click Filter in the step dialog and Create in the left-hand menu. Specify the necessary filters and click Apply to save the changes.

Click Show passing/Failing rows to view the values that passed/failed the filter. The passing and failing results can be individually linked to any further step(s). 

Transform step

This step allows you to ad/remove various functions and arrange/show and hide columns.

See Function editor for more details.

Union step

The union step allows you to combine two inputs into one output by presenting the values on top of each other. The source columns with matching headings are combined.

The output – combined values from two sources – can then be used as input to any further step(s).

Click Show data in the step dialog to view the results.

Validate step

The validation step allows you to identify data that matches the specified rule(s). The results will either be true or false.

To perform validation, you have to set validation rule(s):

  • select from the list of suggested rules (picked up from the input columns) or
  • click Configure rules then add a new rule in the step dialog.

You will be taken to the Function editor, allowing you to define the required rule(s). Once created, apply the rule(s) using the Edit validation configuration dialog.

Click View or sort rows to preview the data that will be validated.

To view validation results, click to see the required data: passing, failing and ignored rows. Each of these results can be further analyzed by linking them to other workflow steps.

To view information on each row, click Show data. You can right-click on a failing row and select Reasons for failed rules to see even further detail.

Click Results by rule to view the overall summary of validation results. 

Validate emails step

Use this step to validate emails based on the format or domain address.

The two validation types are available:

  • Format check - whether the email is in a valid format (a syntax check against the RegEx values as defined in the Glossary)
  • Domain level - whether the email domain is valid/exists or not (a DNS server check to see if the domain exists and is an email server)


You can customize the way emails are validated:

  • specify your company's DNS servers: go to Configuration > Step settings > Validate emails > DNS servers.

    We use a Domain Name System (DNS) lookup to check whether the email’s domain exists. If not specified, the DNS server will default to Google (8.8.8.8) which may be blocked by your firewall. If blocked, use nslookup to find a DNS server that can be used on your network and add it by clicking Add a new DNS server.

  • define email address format: go to Glossary > Constants > Email Expressions > Email Address


By default, the validation results are cached with results refreshed every 30 days. To change this, go to Configuration > Step settings > Email step > Email validation cache validity.

Workflow outputs

This tab lists all the outputs in your workflow, providing a list view which is useful for larger/complex workflows. To quickly find a specific output, right-click on it and select Locate.

Share workflows

Workflows can be shared with other users connected to the same instance of Data Studio. If you share a workflow, other users will be able to access and modify it.

To share a workflow, go to Workflow Designer, select the required one and click Share.

Note that multiple users can edit the same shared workflow simultaneously, and therefore potentially overwrite each other's changes.

All the shared workflows available to you will appear in the Shared workflows list.

To revert access to a workflow, select it and click Unshare. Note that administrators are able to unshare any workflow.

Execute workflows

A workflow can be executed on an ad-hoc basis or configured to be executed in the future (and at defined intervals, if required).

Go to Workflow Designer and click Execute on the workflow you want to execute now or later.

In the Schedule workflow execution dialog specify when and how often you want to execute the workflow and whether to export the output table.  

When scheduling the workflow to be executed later, use the End time type to specify when the End action will be applied. This is the action that will be performed after the workflow is executed. The options are:

  • None – no action performed after execution
  • Kill – if the end time has been specified and the execution hasn’t yet finished, workflow execution will be stopped
  • Reschedule – when the process completes or the end time has been reached, the execution will be rescheduled to run again according to the specified time intervals

You can also choose to export the workflow as a file or a script.

 

Refreshing data sources for workflow execution

When the workflow is re-executed, the data that was most recently read form the data source will be used.

To force all data sources used in a workflow to be automatically refreshed before each execution, the following conditions have to be met:

  • The refresh setting is enabled: select the required workflow and click Edit details. Enable Refresh on execution and click Apply.
  • If the data source is a file, it has to be modified more recently than the last time the data was read.
  • No other workflow that would cause the data source to be refreshed is being executed. This prevents two workflows attempting to refresh the same data source concurrently.

 

By default, the refresh setting is disabled for all workflows. To enable it for all new workflows, go to Configuration > All server settings and enable Default workflow refresh on execution.

Glossary

Define your business terminology, create data tags/scripts and other objects used by workflow steps. You can also view and edit the blocking keys and rulesets for the Find duplicates step.

Note that Consumer users will not be able to see this area. Find out about user roles.

Constants
The constants area shows the existing constant libraries. You can view the standard business constants installed with Data Studio or create your own. The business constants supplied are regular expressions that can be used in transformations to verify the format of commonly found data fields. You can also create and define your own business constant libraries to use in transformations or with custom steps.

Data tags
You can create new and manage existing data tags using Glossary.

Experian Match blocking keys
This contains the default blocking keys used by the Find duplicates step. You can't change the default set but administrators can create their own keys to customize the behavior of the step.

Experian Match rulesets
This contains the default rulesets used by the Find duplicates step. You can't change the default ones but administrators can create their own rulesets to customize the behavior of the step. 

Scripts
The scripts section allows you to define scripts that can be used by the Script steps in a workflow. Scripts have to be added to a script library.

To add a script library, click Create a new script library, enter a name of the library, a description (optional) and click Okay. Note that script libraries can be deleted only when they are empty.

All scripts have to be added to an existing library. To add a new script, click on the required library then click Create a new script. Scripts require a unique name, the area of use, indication of whether they can output logging to a file, the language type and the script code. The script definition can optionally also have parameters to change the script’s behavior.

Configuration

Manage users, configure workflow steps and specify product settings.  

Note that only administrators will be able to see this area. Find out about user roles.

System settings

These allow you to customize Data Studio. You can specify:

  • All server settings
  • Communication
  • Data handling
  • Loading
  • Security
  • Storage
  • System

Find out about how to change the database location or add a SSL certificate.

Step settings

Configure certain workflow steps and their behavior.

  • Enable steps
  • Find duplicates
  • Validate addresses
  • Validate emails

Manage users

All users in Data Studio have to have a role assigned to them.

The following roles are available:

  • Consumer
  • Designer and developer
  • Administrator

Note that these roles can’t be modified.

Capability Consumer Designer and developer Administrator 
Access to Data Explorer Yes Yes Yes
Access to Workflow Designer Yes Yes Yes
Access to Monitoring Yes Yes Yes
Access to Configuration     Yes
Access to Glossary   Yes Yes
Create workflow    Yes   Yes
Delete workflow    Yes  Yes
Edit workflow    Yes  Yes
Execute workflow  Yes  Yes   Yes
Manage Security       Yes
Update licenses        Yes

To create a new user:

  1. Go to Configuration and click Users.
  2. Click Create a new user.
  3. You have to enter the following:
    - Username (can contain alphanumeric characters and underscores only)
    - Display name (no restrictions)
    - Role (choose from one of the three roles)
    - Enabled (specify whether this user is active, i.e. able to use Data Studio)

Note that you can add as many users as you want but the number of active users you can have will depend on your licence. Once your user limit is reached, you won't be able to set users to Enabled.

You can also group users for easier management by assigning them to teams.

Users with the Administrator role will be automatically assigned to the Administrators team. By default, there’s a team called Initial group which you can modify.

Authentication mechanism
This is the method of authentication that will be used. The following options are available:

  • Aperture Data Studio internal – use this for users requiring access to Data Studio only, without any additional authentication.
  • LDAP and MS Active Directory (AD) server – use this to allow users to log in with their LAN IDs (fill in the LDAP username field). If this option is not available, go to Configuration > All server settings and enable LDAP properties. Find out how to configure LDAP.
  • Kerberos server – use the Kerberos username for the login.

To manage user details, go to Configuration Users. Right-click on the required user to edit their details/change password or disable them.

Monitoring

View logs, manage scheduled and running jobs.

Audits 
A list of all audit events such as user logins, viewed data and workflow operations (create, delete, share). 

Jobs
A list of running, queued and completed jobs (e.g. workflow executions).

Log messages
A list of detailed system logs. To download logs as a .txt file, click on your username in the top menu and select Download log files. Note that this option is available to administrators only.

Processes 
A list of temporary processes that may run to support various one-off system operations.

Repository objects
A list of all repository objects (e.g. users, business constants, data tags, regular expressions, data sources).

Restart log 
A list of various checks that the system will run periodically (e.g checking disk space).

Services
A list of permanent processes that support various system operations (e.g job scheduling, housekeeping, loading, alert notification).

Sessions
A list of user and server sessions.

Snapshots
A list of all snapshots. This includes information such as when snapshots were created and how much disk space is currently used. Also, administrators have the option to delete individual snapshots.

Storage
A list of the key sub-directories that form the database.

System
Various statistics relating to the running of the system (e.g. number of CPUs, free memory, network information).

Tasks
A list of running and completed data operation tasks (e.g. load, profile, find duplicates).

Auditing

Data Studio allows you to audit (record and report) user and system activities.

The audits are encrypted and stored in \ApertureDataStudio\data\alerts\audits.txt.

Enabling and tuning
Auditing can produce lengthy logs, not all of which may be essential to your requirements, so to help tailor and target your auditing, you can enable auditing based on specific events.

You can view and change audit options in Configuration > SecurityAudit types.

Once an audit is enabled, every time the event occurs, an entry is created in the audit log, detailing the cause, user information and when it happened.

Audit events

  • Create a workflow: tracks when a workflow is created.
  • Data exported: tracks when data is exported, either via a workflow step or any other method.
  • Data viewed: tracks all attempts to view data in a drilldown.
  • Delete workflow: tracks when a workflow is deleted.
  • Edit workflow design: tracks when a workflow’s design is changed.
  • Edit workflow details: tracks when a workflow’s name or description is changed.
  • Execute Workflow: tracks when a workflow is executed.
  • Repository change: tracks when a repository object is created, edited or deleted. This includes tracking changes to users, datastores, entities, glossary objects and more.
  • Script execution: tracks when a script is executed.
  • User access: tracks user login, logout and login failure events.
  • Workflow shared: tracks when a workflow is shared or unshared.

 

Reporting and monitoring
The events generated by active audits are visible in Monitoring > Audits.

The resulting view shows all the events generated by Data Studio with the most recent events at the top. The view can be sorted, filtered, or exported to a .csv file. This view will also update itself periodically and display any events that have happened in the meantime.

Function editor

When you right-click on a column heading and select Add transformation, you will be taken to the transformation function editor. 

You can try various transformations without affecting data. Your changes will not be saved until you click Apply in the top menu.

The left-hand menu contains two tabs: one for transformation functions and the other for connections

You can either scroll down to find the required function or use the search box to find it then drag and drop in the area on the right.

Example: remove special characters from phone numbers.

  1. Go to Data Explorer > Sample data source.
  2. Drag and drop the Customer V1 file into the workspace.
  3. Right-click on the Telephone column heading and select Add transformation.
  4. In the search field on the left type in Remove noise and click on the function when it’s shown. It will appear in the right-hand side (workspace).
  5. Tick the following boxes: Remove vowels and Remove whitespace.
  6. Click Apply in the top-right corner. You will be taken back to the file view. The  icon will appear next to the Telephone header indicating that values in this column have been transformed.

The Expression preview in the bottom-left corner allows you to see how the data will be transformed if you save your changes. If you're working on a workflow that contains a step that hasn't been executed, you have to click Execute to see the preview of the data. 

You can also see how a transformation will behave by using sample/dummy data: click Single at the bottom and enter the values you want to test.

To revert the transformation, right-click on the column header and select Remove transformation.

You can either continue building on this transformation using the Workflow Designer by clicking Save as workflow or save the data as a .csv file by clicking Download as .CSV.

Transformation functions

Function Description
Add period Add a period to a date/time expression
After Extracts from an input value, everything that follows the search value
And The logical operator and: if both inputs are true it returns true
Before Extracts from an input value, everything that precedes the
search value
Calculate Evaluate a simple mathematical calculation using +,-,/,* and %
Add 
Divide 
Multiply 
Remainder 
Subtract 
Add 
Divide 
Multiply 
Remainder 
Subtract
Check Tests whether a value is valid with any of a wide range of validation tests
Alphabetic Checks that the input value is alphabetic
Alphanumeric Checks that the input value is alphanumeric
Date Checks whether the value if of date data type
Decimal Checks whether the value is a decimal
Note: Integers don’t pass this test
Empty Checks whether the value is empty (spaces or Null)
Error Checks whether the value is drilldown error
Even Checks whether the value is an even number
False Checks if the input is a False value
Integer Checks whether the value is an Integer
Leap Year Checks that the input value is a leap year
Negative Checks that the input value is a negative number
Null Checks that the input is a null value
Number Checks that the input value is a number
Odd Tests if the value is an odd number
Positive Checks that the input value is a positive number
True Checks that the input is a true value
Valid CUSIP Checks if the input is a valid Id defined by the committee on uniform security identification procedures (CUSIP)
Valid International
Securities Identification
Number
Checks if the input is valid International Securities Identification Number (ISIN)
Valid Stock Exchange
Daily Official List
Returns true if the input is valid stock exchange daily official list
(SEDOL) number
Warning Checks whether the input value is a drilldown warning
Chunk Splits the input value into variable length chunks using the
supplied length, returning a list of split values
Combine lists Joins two lists of values in different ways
Difference The symmetric difference, leaving the values from the left and
right that don’t intersect
Intersection The intersection of the two lists leaving only values that match
on both sides
Remove left The values from the right list that don’t intersect the left list
Remove right The values from the left list that don’t intersect the Right list
Union The two lists combined
Common in list Analyses a list and return two values. The first is most or least common in the list, and second is how often it occurred
Least common in list Returns a list of two values by analyzing a list for the least common value. The first value on the returned list is the least common value and the second is number of times it occurs
Most common in list Returns a list of two values by analyzing a list for the most common value. The first value on the returned list is the most common value and the second is number of times it occurs
Common prefix Compares all supplied values and returns the prefix that is common to all of them
Compare Collections of expressions to perform validations. These checks are particularly useful for the text field.
Contains Checks whether the input value contains the check value, optionally ignoring case
Ends with Checks whether the input value ends with a given suffix, optionally ignoring case
Equals Checks the input value for equality with the comparison value, optionally ignoring case
Equals (standardized) Checks the input value for equality with the comparison value,
based solely on appearance i.e. ignoring any differences in datatype
Equals error text Checks if the input values equal a specified error message
Equals warning text Checks if the input values equal a specified warning message
Greater than Checks if the input value is greater than the comparison value
Greater than or equal Checks if the input value is greater than or equal to the
comparison value
Less than Checks if the input value is less than the comparison value
Less than or equal Checks if the input value is equal to or less than the comparison value
Matches expression Checks if the input matches a regular expression
Matches format Checks if the input matches a format pattern
Sounds like Checks whether the input value phonetically matches the comparison value
Starts with Checks whether the input values start with a given prefix, optionally ignoring case
Compare date/time Compares two dates/times and returns the difference in unit
Concatenate Concatenates two variables to form a new single variable
Constant value Returns a specified value
Contains match

(lookup function) Returns true if any element of a list is contained in a lookup column.

The following match types are available:

  • Case-insensitive - ignores the letter case. 
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces é with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits -  as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.

Note that both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation).

Three input types are available:

Space-separated – treat the input value as a single sentence of whitespace-separated words.

Whole value – treat the input value as a single sentence containing only one word (i.e. the whole value is the word).

Comma-separated – treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.

Convert Converts input into another datatype, as selected by the user
Auto convert Auto converts
To alphanumeric Converts any value to an alphanumeric value
To date Converts any value to a date value. For example: 25.12.2009 to 25-Dec-2009
To decimal Converts any value to a decimal value
To integer Converts any value to an integer value
For example: Input Value-12.3  Return Value-12
To null Converts any blank spaces to null ones
To spaces Converts any null values to blank ones
To time Converts any value to a time period
For example: Input Value:00-00-23 Return Value-00:00:23
Convert to date Converts any value to a date value. For example: 25.12.2009 to 25-Dec-2009
Create date/time stamp Creates a time stamp with supplied value year, month, day, hours, minutes, seconds
For example: Year-2009, Month-12, Day-25, Hours-12, Minutes40, Seconds-30
to 25-Dec-2009 12:40:30
Create list Creates a value list from any number of values
Current row Returns the row ID, starting at 1, i.e. the current row is displayed
Current timestamp (dynamic) Returns the current timestamp
Datatype Returns the datatype of the input value
Decode geohash coordinates Decodes a geohash to its latitude or longitude component
Delimited substring Returns the substring of a value using a start delimiter or an index position and either an end delimiter or return value length. For example:
Input value: ABC,DEF, GHI
Delimiter:,
Length:5
Returns value: DEF,G
Difference Returns the portion that is a difference between two values
Edit distance Calculates the Levenshtein edit distance between the input value and the compare value returning the number of edits required to transform the input value to the compare value and vice versa. For example, the edit distance between Frank and Plank is 2, because two characters must be changed to go from Frank to Plank and vice versa. Edits are not just transpositions but also removals and insertions.
Edit distance percentage Calculates the Levenshtein edit distance between the input value and the compare value returning a percentage similarity between the two values
Jaro edit measure Calculates the Jaro-Winkler distance between the input value and the compare value returning a percentage match. The higher the match the more similar the values are.
Jaro-Winkler edit measure Calculates the Jaro-Winkler distance between the input value and the compare value returning a percentage match. The higher the match the more similar the values are. The Jaro-Winkler algorithm is the variant of Jaro algorithm and includes additional checks which test for a common prefix at the start of both the values.
Error message Returns an error message, setting the expression into error and displaying the result in red.
Escape Escapes or un-escapes a string based on a given escaping style.
Example phone number Generates an example phone number for a specified region in various formats
Expand list Extract all members of a list and separate them with a specified delimiter
Extract matches as list

(lookup function) Extracts elements form the input that are contained in a lookup column and outputs them as a list.

The following match types are available:

  • Case-insensitive - ignores the letter case. 
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces é with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits -  as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.

Note that both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation).

Three input types are available:

Space-separated – treat the input value as a single sentence of whitespace-separated words.

Whole value – treat the input value as a single sentence containing only one word (i.e. the whole value is the word).

Comma-separated – treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.

Extracts from list Parses a value that is a list of comma separated values allowing any value to be extracted from the list by given index number
Extracts timestamp element Extracts a defined element from a date/time value
Get age Age
Get century Century
Get century Get a defined week from a date/day value
Get day of week name Get a defined name from a date/day value
Get days Days
Get hours Hours
Get millis Milliseconds
Get minutes Minutes
Get month name Get a defined name from a date/month value
Get months Months
Get seconds Months
Get weeks Weeks
Get years Years
First non-null Given a variable number of input values, it returns the first value
that is not null (empty)
Format phone number Properly format a phone number or extract a specific attribute from it
Format date Formats a date/time with custom date format
Geohash coordinates Converts latitude and longitude to a geohash
Get cell Returns a value from a cell at a given row/column position from the source of current drilldown
Hash code Returns a generated MD5 hash code for the input value
If then else Checks the condition field for the value of true. If its true returns the condition met or else. Can also have multiple condition fields to be evaluated if a precursory condition is not met
In length range Checks whether the input is within the specified start and end length range
In range Checks whether the input is within the specified start and end range, optionally ignoring case
Index of difference Returns the position where two values begin to differ, starting from 1 or 0 if there is no difference
Initials Returns the initials by splitting words from the input value with an optional list of delimiters (default is white space)
Insert Insert a value into the input value at a given position
Length Returns the number of characters in the given string
List Transform a list of values using a variety of operations. Multiple operations can be used in a cumulative manner.
Common Prefix Compares all supplied values and returns the prefix that is common to all of them
Deduplicate Deduplicate all values in a list
Reverse Reverse all values in a list
Reverse sort Reverse sort all values in a list
Sort Sort all the values in a list
List frequency Returns unique values in the list interleaved with their count (frequency)
Lookup aggregate (lookup function) Looks up a specified column in another table, and then returns another specified column if the lookup succeeds or null if it fails
Lookup check (lookup function) The lookup check
Lookup count (lookup function) The lookup count
Lookup list (lookup function) Looks up a specified column in another table, and then returns another specified column if the lookup succeeds or null if it fails
Lookup value (lookup function) Looks up a specified column in another table, and then returns another specified column if the lookup succeeds or null if it fails
Multi compare Performs multiple compare operations in one transformation. Returns true if any of the individual arguments are true.
Contains Checks whether the input value contains the check value
Ends with Checks whether the input value ends with a given suffix
Equals Checks the input value for equality with the comparison value
Equals (standardized) Checks the input value for equality with the comparison value(s) based solely on appearance i.e. ignores differences in datatype.
Equals error text Checks if the input value equals a specific error message
Equals warning text Checks if the input value equals a specific warning message
Greater than Checks if the input value is greater than the comparison value
Greater than or equal to Checks if the input value is greater than or equal to the comparison value
Less than Checks if the input value is less than the comparison value
Less than or equal to Checks if the input value is less than or equal to the comparison value
Matches expression Checks if the input value matches a regular expression
Matches format Checks if the input value matches a format
Sounds like Checks if the input value phonetically matches a comparison value
Starts with Checks if the input value starts with any given prefix
Not Turns the input value from true to false or vice-versa
Or The logical operator or; if either input is true it returns true
Pad Pads any value with a chosen character to achieve a given overall length, with the option of putting the padding before or after the given value.
Partition first Returns the list of 3 values ['before', 'fragment', 'after'], where 'before' is the part of input value before the 1st occurrence of the fragment and 'after' is what’s left. A fragment is a regular
expression.
Partition last Returns the list of 3 values ['before', 'fragment', 'after'], where 'before' is the part of input value before the 1st occurrence of the fragment and 'after' is what’s left. A fragment is a regular
expression.
Parse Parses a value, extracting other values which match one of the supplied formats. This can be used to extract values that look like an expected type of value from a free-text value. For example, an embedded postal code in an address string. The result can be deduplicated, comma separated quoted list of values for each result found. Multiple patterns can be searched at the same time and they are searched in sequence.
Parse by format Example: if the input value is 'abc1234def5678' and the format pattern to search for is '9999', then the result will be '1234,5678'.
Parse by regular expression Example: if the input value is 'abc1234abc5678' and the expression to search for is 'a.c', then the result will be 'abc,abc'.
Power of n Returns the nth power of the input value
Position Returns the first index position, starting from 1, of a search value within the input value
Phone number match Tests if two numbers represent exactly the same number
Phone number match code Compares two numbers and returns the match code
Remove matches

(lookup function) Removes elements from the input that are contained in a lookup column.

The following match types are available:

  • Case-insensitive - ignores the letter case. 
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces é with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits -  as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.

Note that both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation).

Three input types are available:

Space-separated – treat the input value as a single sentence of whitespace-separated words.

Whole value – treat the input value as a single sentence containing only one word (i.e. the whole value is the word).

Comma-separated – treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.

Remove noise Returns the input value after standardizing it by removing noise from the value. Initially, the value is transformed by translating all letter to uppercase and retaining only letters and digits.
Repeat Returns an input value repeated a defined number of times.
Example:
Input Value: ABC
Repeat: 3
Return Value: ABCABCABC

Replace

Replaces all the instances of the search value with the replacement value.  Example:
Using the input value 'ABC:DEF:GHI', the search value ':' and the replacement value ';' will return  'ABC;DEF;GHI'

Regular expression replace

Replaces all the instances of the search value with the replacement value. Example:
Using the input value 'ABC::DEF:::::::::GHI', the search value ':+' and the replacement value ';' will return 'ABC;DEF;GHI'

This differs from the 'replace' function as the search value can be a regular function as opposed to a constant value.

Replace first Replaces first the instances of the search value with the replacement value. Example:
Using the input value 'ABC:DEF:GHI', the search value ':' and the replacement value ';' will yield the result 'ABC;DEF:GHI'
Replace matches

(lookup function) Replaces elements from a list with matching elements from a lookup table.

The following match types are available:

  • Case-insensitive - ignores the letter case. 
  • Normalized - ignores the letter case and replaces accented characters with normalized versions (replaces é with e).
  • Standardized - as Normalized but also replaces punctuation with spaces.
  • Standardized: no digits -  as Standardized but also removes numeric digits.
  • Exact - matches the exact letter case and format.
  • Format - matches the format only.
  • Regular expression - treats the lookup column values as regular expressions for matching purposes.

Note that both Standardized and Standardized: no digits also change the meaning of whitespace when splitting a sentence up into words (i.e. they cause the sentence to also be split on punctuation).

Three input types are available:

Space-separated – treat the input value as a single sentence of whitespace-separated words.

Whole value – treat the input value as a single sentence containing only one word (i.e. the whole value is the word).

Comma-separated – treat the input value a comma-separated list of sentences. Each sentence is treated as whitespace-separated words.

Row count Returns the total number of rows in a view
Round number Sets the number of decimal points after a decimal value
Split Splits a value using another value in the field separator
Strings between Searches a value for substrings delimited by a start and end tag, returning all matching substrings in a list
Strip substring Strips the supplied substring value from the given input value if present. Example:
If input value is 'abcdefghi' and search value is 'def', the result would be 'abcghi'.
Substring Returns substring of a given value specified by giving start and end positions
Tag a value Adds the start and end value to the input value if they are not present in their respective positions. Example: Input value: 12345> Start Value: < End Value: > Return value: <12345>
Transform number Transforms a number using any of a wide selection of mathematical and scientific conversions
Absolute Returns the absolute value of the integer
Arccosine Returns the trigonometric arc cosine of the input as an angle in radians, in the range of 0 through to pi
Arcsine Returns the trigonometric arc sine of the input as an angle in radians, in the range of -pi/2 through to +pi/2
Arctangent Returns the trigonometric arctangent of the input as an angle in radians, in the range of -pi/2 through to +pi/2
Cosine Returns the trigonometric cosine of an angle in radians
Cube Returns the cube of a number
Cube root Returns the cube root of a number
Euler raised Returns Euler raised, e, raised to the power of input value
Log base 10 Returns the logarithm of any number, to base 10
Natural log Returns the natural logarithm of any number, which is the log to base e
Negate Converts a positive number to negative and vice-versa
Precision Returns a numerical precision of input value - a number of significant digits
Random integer Returns a random integer between 1 and the inputted value
Random number Returns a random number between 0 and the supplied maximum value
Scale Returns a numeric scale of a value - a number of significant digits after the decimal
Sine Returns the trigonometric sine of an angle in radians
Square root Returns the square root of a number
Sum digits Returns a sum of numeric digit of any value. Example:
Input value: 546.2
Sum digits: 17
Tangents Returns the trigonometric tangent of an angle in radians
Transform text Transform a text value using a variety of operations. Multiple operations can be used in a cumulative manner.
Compact spaces Reduces multiple spaces in input value to single space
Double Metaphone Implement double Metaphone algorithm developed by Lawerence Phillips
Double Metaphone (alternate) Implement alternate double Metaphone algorithm developed by Lawerence Phillips
Extract integer Extract all values that are numbers out of embedded space or letters. Contiguous integers are extracted.
Fingerprint Returns a fingerprint of an input value
Format pattern Returns a format pattern of an input value
From hex to text Converts a hexadecimal input into a string
From text to hex Converts a string input into its hexadecimal value
From Unicode to text Converts a Unicode input to a string
From text to Unicode Converts an input string into its Unicode value
Improved Metaphone Implements the improved double Metaphone algorithm
Improved Metaphone (alternate) Implements the improved double Metaphone algorithm for alternate value
Lowercase Converts to lowercase
Refined Soundex Implements refined Soundex algorithm
Remove noise Returns the input value after standardizing it by removing noise from the value. Initially, the value is transformed by translating all letter to uppercase and retaining only letters and digits.
Remove unprintable characters Removes unprintable characters in the input value
Remove whitespace Remove whitespaces in an input value
Remove simple format pattern Simple format pattern is just like a format pattern, just that, it reduces multiple format characters to just one. Example:
Format: AAAAA9999AA
Simple format: A9A
Soundex Soundex is an encoding used to relate similar names, but can also be used as a general-purpose scheme to find words with similar phonemes
Standardize Standardizes a value by removing leading, embedding & trailing spaces, converting white space and punctuations to a standard space, except for quote & period characters which are removed. Digits are preserved, letters are preserved as they are lower case, Unicode normalized version. This function is used for comparing values in a domain.
Strip accents Removes diacritics ( ~= accents) from a value
Title case Converts to title case
To proper case Converts to proper case where the first letter is in uppercase whereas all others are in lower case
Uppercase Converts to uppercase
Trim Removes all the instances of a single character from the start of the input value
Unquote value Removes the quote value from the start and/or end of the input value, but leaves the respective part of the value alone if the quote is not present. Example:
Input value:ABC12345ABC
Value to remove (case sensitive): ABC
Returns value: 12345
Validate phone number Validates a phone number according to a specific test
Warning message Returns a warning message and displays the result in yellow