-
Sneak peak of new data quality offering: Let us know what you think
Hi everyone, In this post I'd like to provide you all with a sneak peak of a new real-time data quality enforcement offering that we're currently exploring, that we'd love to get your feedback on (complementing our existing contact data validation products). The idea centres around exposing Data Studio functionality (e.g.…
-
Rows to Columns
Hi, I have been testing out the Rows the Column function. However, it seems to be not pulling through all the columns. In the DEV data set, I currently have 62 rows which I would like converted to columns. When using the Rows the column, it automatically only finds around 56. Wondering if anyone else has had a similar…
-
Flattening out / Pivoting / Transforming results
Hi, I'm having an issue flattening out results. Essentially, I have multiple rows for the same customer because they have different PII's. Is there any way of transforming / pivoting / flattening out the results to have one row for each customer? I have left a screenshot of mock-up data below to help people understand. I…
-
Compare Dates (Verbose) 📅📅
Compare Dates (Verbose) [Category: Conversion] This function provides a summary of how 2x input dates compare (and also includes simple 'convert to date' logic to aid the analysis). Output options are: Exact match Incomplete - Date 1 is missing (or not a date) Incomplete - Date 2 is missing (or not a date) Incomplete -…
-
ℹ How to use Functions in Workflows
Workflows Workflows is the area in Data Studio where designer users tend to spend most of their time. For anyone new to it, imagine a data pipeline that manipulates one or more data sources through a number of stages and then does something with the results. Whilst a Workflow is easy to build by connecting different steps…
-
More detailed manuals/examples/syntax
Hello. I'm new to Experian Aperture Data Studio V2. I'm using the documents/help at the Data Quality User documentation site. I'll give you an example. I'm trying to use Get age to filter a dataset down to records with an effective date (one of our fields) to only the current year. The Get age tells me "Returns the number…
-
♻ Reusable workflow example – calculate word frequency
Today I’d like to share with you a reusable workflow that I built a little while ago not only as a downloadable .dmx file (see below) for you to explore/use as you wish, but also as a means of demonstrating one of the key themes of Data Studio…reusability. If you find that there are duplicate tasks being executed on your…
-
Working hours between 2 timestamps
Hi All, Is there a way to calculate the working hours between 2 timestamps - start date and end date. We would like make this calculation for our KPI measures. We need to exclude the weekend hours as well somehow, so the result should be the exact hours, minutes, seconds. Let me know if you have any ideas! Many thanks!…
-
Finance Format Number 💲
Summary This package contains a single function but may be updated in the future to include others. Finance Format Number 💲 Suggested by @Sharat Kutty, this function converts Negative numbers are put it in brackets so the number i.e. -100.23 becomes (100.23) and shows up as Red color. All 0 and above shows in normal color.…
-
Percentage functions 🧮
Summary This package contains a couple of functions but may be updated in the future to include others. Format Number as a Percentage 🔣 This is a function, suggested by @George_Stephen and intended to be used to help create a presentation form of percentages for use on dashboards/views. The function takes a numeric input…
-
📋 Index of reusable Functions! 📇
This post simply acts as an index for all the functions shared in this library. If you would like to receive a notification whenever a new function is added, please bookmark this post by clicking on the bookmark icon to the right of the title. Current functions available: 👪 Parse Full Name - A handy set of functions that…
-
UK Postcode formatter 🏡
Summary This package contains a single function but may be updated in the future to include others. UK Postcode formatter 🏡 This function simply adds a space between the incode and the outcode for a given UK postcode (e.g. SW1A1AA becomes SW1A 1AA). See below for preview of the function definition logic and some sample…
-
Use of If-Then-Else logic in Aperture Data Studio
Imagine you are asked to use Aperture Data Studio to generate a new field for your sales data. You have the input fields: Discount Code, Quantity and Price. The ask is to generate a new field “Offer Price” using the following logic: How can you do this in Aperture Data Studio? The answer is to build a custom transformation…
-
Standardise Country 🌎
Standardise Country The function uses reference data (contained within the .dmxd file) to standardise known country aliases to the standard form (e.g. England = United Kingdom). See below for a preview of the logical definition of the function and a preview of the dataset that it comes with. If, after processing your data,…
-
Why this function is not working when using replace matches?
Good afternoon, Could you please help me to undertand why the following function isn't returning the desired outcome. Here is the what it should be doing: Looks if the combination of two columns have a match between the tables i.e. I’m using the match table 1 - Job title/table 2 - position name and table 1 - job…
-
A Function with two parameters is not showing up in a workflow
I created 2 parameter Re-usable function its working and showing results. When I try to use it in Workflow its not coming in the functions list please let me know if I am missing any thing.
-
Future Date Check 📅 (Dynamic)
Summary This package contains 2x rules: “Is a future date” and “Not a future date” (compared to current date at time of execution) which can help assessing whether certain date values are fit for purpose: Is A Future Date? [Category: Validation] Checks that the input date is a date in the future (relative to the date at…
-
Convert Epoch Date/Time to Standard Date/Time ⌚
Convert Epoch Date/Time to Standard Date/Time ⌚ This function was inspired by this post and created to support any users exploring use of the Data Studio API as a source to generate meainginful MI (e.g. reporting on sessions/events/activties/jobs/users and more) as Data Studio's publishes times in the epochtime format…
-
Functions - suggested product improvement
Hi, When working with a list of functions, every time I edit/publish/share one it returns me to the top of the list and I have to find my place again to do more work on the same Function - not essential, just a bit frustrating... Currently using v2.9.6 Any chance of making an amendment so you are returned to the same…
-
Lookup step returning unexpected values
Hello. I am currently using version 2.8.8.27. I've discovered recently that in one of my workflows, the Lookup step is not returning the expected results in all cases. I have quickly set up a test scenario I have attached below. The idea is the following: The lookup should give back the "Value" column's values in cases…
-
Validate Email Domain. Accuracy?
Hi, I have the validate email address (domain) function up and running, but I am not sure about the accuracy of the results. For example, the following email gets flagged as bad, even though the domain is real (you can google it). FYI I am using Amazon AWS DNS server (169.254.169.253) in the Aperture settings.…
-
Is there an easy way to filter out records which contain a number only (for a given column)?
I need to filter out records which contain a number only from an address column. I have attempted to do this using the To Number and Is Number functions: This approach does not work because the To Number function does not behave as I would expect it to: Why does 2 Street and 4Street not return the Invalid number error? Can…
-
Repeating Characters 🔁
Check for same character repeated This function uses a regular expression to identify records where the entire value of a cell is made up of the same character repeated (e.g. "aaa" or "0000000"). The idea here is to detect cells which are clearly just entered with default values (either through automated processing, e.g.…
-
Extract First Word 🥇📝
This function extracts the first word in a string, in which all words are separated by space. The function returns the first word, regardless of if the string starts with space. If the string contains of only one word, it returns the word as it is received in the input. The function can handle space before the word. If in…
-
Replace the word ‘NULL’ or any non-null space values with null 🔄👻
This function replaces the word ‘Null’ (case insensitive) with a null value. For example, if the value of a column has been visible and people can read the word ‘Null’, once it is replaced this column will have an empty value. Moreover, if there are any empty values, which however are not read by Aperture Data Studio as…
-
Extract Last Word 💬🥉
Extract Last Word Version I This function extracts the last word in a string, in which all words are separated by space. If the string contains of only one word, it returns the word as it is received in the input removing any starting space. The function can handle space before the word. If in the input there is a single…
-
Functions
Hi, I'm working with Functions. Re:product development, it would be a really useful to be able to multi-select Functions, eg when applying Sharing, Deleting etc, rather than having to do each one individually. Thanks (in hope and anticipation) Nige
-
Parse Date 🧽📅
Parse Date This function is designed to have flexibility to accommodate all sorts of different date formats, which can be configured by the user. In addition to mapping the input, as you can see, the user is prompted for a 'format' when using this function and this can be configured intuitively as you can see in some of…
-
How to highlight null/empty values
When performing exploratory data analysis, or presenting data in grids in a dashboard, you may want to highlight null/empty values. Within Aperture, you can build a custom function to do this and apply it to one or more columns in your dataset. Here is an example: Go To Functions, Create new function. Create a parameter…
-
What "4016: Functions exception occurred" means?
Hi, I'm creating a workflow with a large set of validation rules (110 rules and 74 groups) and when trying to see the results I'm getting the "4016: Functions exception occurred". I've tried to find the answer in the documentation but no lucky. Could you please let me know if I'm missing something. Thanks
-
How to check if an input value contains numbers
In Aperture Data Studio, in order to check if an input value contains numbers, you can use the Matches Expression function. The Matches Expression function uses a regular expression to specify a pattern to be matched against. If you are not familiar with a regular expression, it may take some exploration to get to the…
-
Calculate the distance between two sets of co-ordinates 🌍️
Summary Returns the approximate distance in kilometres between a pair of geographical co-ordinates. Input latitude1, longitude1, latitude2, longitude2, output value is distance between each set of co-ordinates while considering the spherical nature of earth using the law of haversines. Example Buckingham Palace, London is…
-
Next ⏭️📅 & Previous ⏮️📅 Working Day
Summary The 2x functions contained within this package relate to identifying the last working date before and next working date after given input (i.e. Excluding Weekends). Note that this function does not take into account Bank Holidays. Get Date of Next Working Day Returns the next working day after a given input (i.e.…
-
Last day / working date of month 📅
Summary The 2x functions contained within this package relate to identifying the last date of the month (and the last working day of the month) for a given input. Last Date of Month This function returns the last date of the month for a given input to help make it easier to assess any business rule logic that requires this…
-
Exact Match with List functions
There are a number of List functions that may be useful when you are trying to de-duplicate a list of values within a single column. Let's take a look at this dataset. How can we de-duplicate the list of fruits for each day? Connecting the dataset to a Transform step with List Frequency and List De-duplicate Functions will…
-
👋 Introduction to the Functions Library 📂
Intro This area of the Community hosts our library of re-usable functions for use in Aperture Data Studio. Whilst Aperture Data Studio comes with a wealth of native functions out-of-the-box, this area of Community has been established to provide a reference library of further re-usable functions that you can easily add to…
-
Make your own reusable RIGHT function to return a number of characters from the end of a text string
A customer was asking if there is a way to achieve something similar to the RIGHT() function within Aperture Data Studio. We do have a flexible Substring function that you can use. Use -1 to indicate that the End position should be the last character of the Input value. Specify any negative number to return a number of…
-
IF then ELSE Logic
Can I write IF (Some Condition)THEN (Valid) Else (Invalid) Logic in Aperture? So That in the Output Result set I can view both Valid and Invalid Result set
-
👪 Parse Full Name
Summary The functions contained within this package are all designed to help you in dealing with names data; specifically when a full name is contained within a single field. The functions, detailed below, help with resequencing names when commas are present (e.g. “Danny Roden” vs “Roden, Danny”) and then with parsing out…
-
Offensive Words 🤬
Summary The functions contained within this package all relate to flagging and dealing with data containing offensive language. All of these functions uses a domain of offensive words (contained within the .dmxd package) which contains a list of known offensive terms. Note: this data is open source and originates from:…
-
Mask Out Email/Phone 🛡️
Summary This package contains 2x functions to help with anonymising certain input values, whilst leaving an output that can still be used for non-sensitive analysis. Mask Out Email This function masks characters in the sensitive part of an input email address with 'X' characters, leaving the domain untouched (e.g.…
-
Proper Case Surname 📛
Summary This package contains 2x functions which help with contact data: Proper Case Surname and Validate Surname Casing. Proper Case Surname As the name suggests this function, produces a proper-cased Surname for a given input, taking into account some key exceptions including: Irish & Scottish names (e.g. O'Neil and…
-
Get Word ✂️
Get Word Extracts the 'nth' word from a string (where 'n' is defined when the function is executed). Where n = 2 the second word is retrieved, where n = -1 the last word is retrieved. This is useful as a building block for building other functions (such as match keys or for defining parsing logic). See below for a…
-
Reasonable Date of Birth (DOB) 🔞
Reasonable Date of Birth (DOB) [Category: Validation] This function simply checks the input seems reasonable as a valid date of birth. This involves checking that the value is: (i) A date, (ii) Not in the future and (iii) Related to an individual within a specified age bracket (set with a parameter). See below for the…
-
Standardise Gmail addresses 📧
Standardise Gmail addresses [Cleansing] This function is used to help standardise Gmail addresses for matching purposes. The function creates a normalised version of the input email address to help aid matching processes. See below for a screenshot showing the logic definition and some sample output results: As you can…
-
Job Title Match Key 👨💼👩💼
Job Title Match Key The function in this post has been designed to help illustrate an approach (and act as a template for you to build on) to help handle inconsistencies with Job Titles found in B2B databases. In short, the function generates a key that can be used to group job titles together (despite presentation…
-
Invalid Character for Names ☹️
Summary This package contains 2x functions: Contains Only Valid Characters for Names & Contains Invalid Character for Names Contains Only Valid Characters for Names This function finds records where the field contains only characters which are valid for names. Records which contain digits, commas and other…
-
Convert Boolean ✅❌
Convert Boolean [Category: Conversion] This function converts a binary or Boolean values (i.e. true/false, pass/fail, 1/0) to a graphical emoji icon (✅ or ❌) to aid visual presentation in the UI. See below for a preview of the function definition along with some outputs for different input values: Using Data Studio's bulk…
-
Contains Non-Latin Characters 🈯
Contains Non-Latin Characters [Validation] This function identifies the presence of any characters not in the 'basic Latin' Unicode block, see here for more info: . This may be useful in verifying assumptions about data expected to be in Latin character sets only (particularly when downstream processes require it to be).…
-
Reverse String ⏪
Reverse String If you find yourself needing to reverse a string (i.e. for bespoke building of anonymisation logic or so as you can sort on suffix) then this function may be of use to you. The function simply reverses the input (as in displays it in backwards sequence) character-for-character (e.g. "Danny R" becomes "R…
-
PCI Detection 💳 (Payment Card Information)
Summary The following package is used to detect payment card information (PCI) and whether it is present or not. Contained within this .dmx package are 2x functions: Contains PCI and Does Not Contain PCI see below for a brief summary of each: Contains PCI [Category: Validation] Checks that the input does not contain either…
-
SIC Conversion 🏷️
Summary The following package is used in processing Standard Industry Classification (SIC) codes, and were designed to help with post-processing the 2007 variant of the SIC code that is output when performing UK Business data enrichment using Experian cleansing. Contained within this .dmx package are 2x functions: Convert…
-
Sharing of useful functions to support contact data validation
Hi all, I've found myself using a few functions on a regular basis so thought I'd share them on here in case they are of benefit to anyone else. The attached .dmx file contains the following functions: Free from PCI info (checks an input does not contain 16-digit numbers either together or separated by hyphens or spaces…
-
Function to calculate the number of working days between two dates
Recently I wanted to create a function in Data Studio to calculate the number of working days between two dates, similar to Excel's NETWORKDAYS function. First, a couple of comments: Weekend days are defined as Saturday and Sunday. Being able to handle alternative weekend days (like the Excel NETWORKDAYS.INTL function)…
-
How to systematically sample with a fixed sample size and varying population size
I was recently asked how to build a Data Studio workflow that would output a systematically sampled set of records with a fixed sample size (e.g. 1000 records), where the number of records in the input will vary. Here's my approach, let me know if you have a better one! Requirement We want to sample every nth record from…
-
Extracting a specific piece of information from an address using regular expressions and lookups
Data Studio has several built-in functions to allow you parse strings of text and extract a particular substring that contains the information you need. A common example would be to extract a postal code, state or country from a postal address, where the whole address is a single comma-separated string. Typically this…
-
Getting started with Regular Expressions in Data Studio
A regular expression (from here on we’ll call them “regex” for short) is a well-known and powerful syntax for describing a search pattern in a simple string of text. You can think of regex as a way of defining advanced wildcards: In Windows file explorer you might search for *.txt to return all txt files, or in SQL you…
