-
Looping workflows
About the requirement Recently I’ve been asked a couple of times if it’s possible to cycle through a workflow for every value within a given column. This is mainly to drive 2x types of use case: Dynamic data export – effectively taking an input dataset and splitting it out into a separate file for each subset of data (as…
-
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 -…
-
What is the best way to deploy through different Aperture Environments, dev -> uat -> prod?
We have the following structure for some of our processes (there will also be a prod but the structure will be identical to below): The idea is that we have a space where all of the core datasets reside - from these, views are created. And these views are shared and used within any workflows we create in different spaces.…
-
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…
-
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…
-
Word Frequency
Every now and then a scenario crops up where it'd handy to know how often a given word occurs within a given dataset. For example you're profiling a reasonably standardised list of values (e.g. job titles) and you want to identify unusually common terms (like 'manager' 'executive' etc) or infrequent ones (like 'test123').…
-
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…
-
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…
-
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…
