๐ Index of reusable Functions! ๐
Danny Roden
Administrator
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 can help standardise the sequence full names are presented in (e.g. "surname, forename" vs "title forename surname") and can extract individual name elements (title, forename, middlename and surname) using logic.
- Next โญ๏ธ๐ & Previous โฎ๏ธ๐ Working Day - As per the name, these functions detect the next working day after (and previous working day before) a given input date.
- Last day / working date of month ๐ - These functions identify the last day (and last non-weekend day) of the month for a given input.
- Parse Date ๐งฝ๐ - This configurable function allows the user to specify a date format string and then it auto-parses in line with that specified format.
- 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).
- Offensive Words ๐คฌ - 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.
- Mask Out Email/Phone ๐ก๏ธ - 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.
- Proper Case Surname ๐ - This package contains 2x functions which help with contact data: Proper Case Surname and Validate Surname Casing.
- Get Word โ๏ธ - Extracts the 'nth' word from a string. Where n = 2 the second word is retrieved, where n = -1 the last word is retrieved.
- Reasonable Date of Birth (DOB) ๐- Checks the input is a value which seems reasonable as a valid date of birth using user defined min/max age parameters (i.e. a date, not in the future, related to an individual within an age bracket of 16-100 years old)
- Standardise Gmail addresses ๐ง - Standardise Gmail addresses for matching purposes (e.g. googlemail/gmail as well as email addresses associated with the same account via the use of '+' and '.' in the account part of the email)
- Job Title Match Key ๐จโ๐ผ๐ฉโ๐ผ - Generates a key that can be used to group job titles together (despite presentation differences)
- Invalid Character for Names โน๏ธ - Finds records where the field contains characters which are invalid for names. Records which contain digits, commas, and other special characters will yield a "true" result. Apostrophes, dashes and periods are not considered "special characters" for this function. This function is not suitable for Validation Rules -- use "Contains Only Valid Characters for Names" instead.
- Compare Dates (Verbose) ๐ ๐ - Provides a summary of how 2x input dates compare (includes 'convert to date' logic). Output options are: Exact match, Incomplete - Date 1 is missing (or not a date), Incomplete - Date 2 is missing (or not a date), Incomplete - Both dates are missing (or not a date), Close - Day & Month match, Close - Month & Year match, Close - Day & Year match, Other difference
- Convert Boolean โ โ- Converts binary or Boolean values (i.e. true/false, pass/fail, 1/0) to a graphical emoji icon to aid visual presentation in the UI.
- Contains Non-Latin Characters ๐ฏ - Identifies the presence of any characters not in the 'basic Latin' unicode block.
- Reverse String โช - Reverses the input (e.g. "Danny R" becomes "R ynnaD")
- Repeating Characters ๐ - 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").
- PCI Detection ๐ณ (Payment Card Information) - Checks that the input contains 16 char numbers (either as a single string or separated with hyphens/spaces every 4 characters) [AmEx format also accounted for]
- SIC Conversion ๐ท๏ธ - Takes a 2007 format SIC code as an input and returns the high level label of the 'category' of businesses which it falls into.
- Future Date Check ๐ (Dynamic) - Checks that the input date is a date in the future (relative to the time of execution).
- Extract First Word ๐ฅ๏ปฟ๐ - retreives 1st word in a string
- Extract Last Word ๐ฌ ๐ฅ - retreives last word in a string
- Replace the word โNULLโ or any non-null space values with null ๐๐ป - produces a 'null'
- Calculate the distance between two sets of co-ordinates ๐๏ธ - Returns the approximate distance in kilometres between a pair of geographical co-ordinates
- Convert Epoch Date/Time to Standard Date/Time โ- converts a timestamp from epoch format to 'standard' format
- UK Postcode formatter ๐ก - adds a space between the incode and the outcode for a given UK postcode (e.g. SW1A1AA becomes SW1A 1AA)
- Finance Format Number ๐ฒ - places negative numbers in brackets and formats in red colour to help aid better visuals on dashboards and views.
- Percentage functions๐งฎ - functions that help users calculate a percentage (given 2x inputs) and another which formats a % value for presentation purposes (e.g. on views/dashboards).
Tagged:
7
Comments
-
As mentioned above, if you bookmark this discussion then you can be alerted via Email as it is updated in future.
More information on email notifications here: https://community.experianaperture.io/discussion/548/email-notification-of-new-aperture-data-studio-releases
2 -
2x new posts added: Standardise Country ๐ & Offensive Words ๐คฌ
1 -
3x new entries added to help you with dates: Next โญ๏ธ๐ & Previous โฎ๏ธ๐ Working Day, Last day / working date of month ๐ & Parse Date ๐งฝ๐ (with more coming soon too!)
1 -
4x new entries added:
- Extract First Word ๐ฅ๏ปฟ๐ย - retreives 1st word in a string
- Extract Last Word ๐ฌ ๐ฅย - retreives last word in a string
- Replace the word โNULLโ or any non-null space values with null ๐๐ปย - produces a 'null'
- Calculate the distance between two sets of co-ordinates ๐๏ธย - Returns the approximate distance in kilometres between a pair of geographical co-ordinates
0 -
1x new entry for:
- โ Convert Epoch Date/Time to Standard Date/Timeย - converts a timestamp from epoch format to 'standard' format
0 -
1x new entry for UK postcode formatter which adds a space between the incode and the outcode for a given UK address:
1 -
1x new entry for Finance Format Number which formats numbers in a manner that's more consistent with financial reporting (negative numbers in brackets and presented in red colour) mainly for presenting in Views and on dashboards:
0 -
2x new functions added today that help users calculate a percentage (given 2x inputs) and another which formats a % value for presentation purposes (e.g. on views/dashboards):
1
This discussion has been closed.