-
📋 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…
-
💡 Submit your ideas 💡
If you've got requests for additional functions, domains or reference data that you'd like to see in this space please let us know. When submitting your request, please try to clearly outline what the function needs to achieve e.g. data looks like [THIS] to begin with, and the function needs to do [THIS] in order to make…
-
👋 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…
-
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…
-
Useful Function: Distance between two coordinates
Recently developed a function based on the Haversine Formula to generate the distance between two coordinates (in meters). It's useful for comparing distance approximations between different address points. Simply copy the below text into the function script editor: param Point 1: Latitude; param Point 2: Latitude; param…
-
Percentile calculation
Hi everyone, I created a reusable workflow that define wheter a column value is in the 'Percentile' value (for example 95%) or not and also creates a column indicating if you need to smooth a value when reach or surpass that percentile. It needs a column and a parameter to the percentile to calculate (1%, 5%, 25%, 50%,…
-
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 -…
-
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…
-
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…
-
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,…
-
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…
-
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…
-
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…
-
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…
-
👪 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…
