| Category | Script Name | Description | Release Date |
|---|---|---|---|
| Analysis | Pre-built robot for Active Directory Contacts | Intended for Results Administrators, this pre-built robot can be used to automatically import specific user contact information from Active Directory and push the data into the Contact Book table in the Reference Collection in Results. The process allows integration of contacts from Active Directory into HighBond so that Projects users can directly assign items to non-HighBond users without typing in their email address. | 2020-04-27 |
| Importing | JHU COVID-19 Case Data Scripts V1.1 | The JHU Covid-19 Case Data Scripts are no longer available in ScriptHub. You can deploy these scripts via toolkits.highbond.com, or contact Support. | 2020-09-03 |
| Data Quality | Remove Empty Trailing Fields | Intended for use with tables imported from Microsoft Excel, removes empty trailing fields with naming convention Fn, for example: F7. | 2020-02-11 |
| Parsing | Text To Columns | Creates a separate field for each segment of text within a character field, based on one or more user-specified delimiters. | 2020-02-11 |
| Isolating | Inactivity Over Time | Reports entities with gaps in their activity of greater than N days at any point during the investigation period. The unique entities can be identified by one or more fields. | 2020-02-11 |
| Isolating | Inactivity Over Time - Single Field | Reports entities with gaps in their activity of greater than N days at any point during the investigation period. The unique entities can be identified by a single field. | 2020-02-11 |
| Isolating | Activity Within N Days | Reports transactions made by the same entity that occur within N days of each other (counting weekend days). The unique entities can be identified by one or more fields. | 2020-02-11 |
| Isolating | Activity Within N Days - Single Field | Reports transactions made by the same entity that occur within N days of each other (counting weekends). The unique entities can be identified by a single field. | 2020-02-11 |
| Isolating | Activity Within N Days - Working Days | Reports transactions made by the same entity that occur within N days of each other (not counting weekends). The unique entities can be identified by one or more fields. | 2020-02-11 |
| Isolating | Activity Within N Days - Working Days - Single Field | Reports transactions made by the same entity that occur within N days of each other (not counting weekends). The unique entities can be identified by a single field. | 2020-02-11 |
| Machine Learning | TF-IDF Text Data Preparation for Machine Learning | Reads a column of text records, and finds the Term-Frequency-Inverse-Document-Frequency (a measure of importance) of each word per record (document) - in context of the entire field (corpus). The goal is to change non-categorical text data, into categorical data (converting text to have numeric values), enabling Analytics Machine Learning commands to use the text field as a feature for clustering/training the ML model. | 2019-08-28 |
| Machine Learning | Elbow method - Sum of Squared Errors (SSE) for K | Finds the sum of squared errors (SSE) for incremental values of k, outputs the value for each k iteration into a table. The values can be graphed and the elbow method used to find the appopriate value of k. | 2019-08-28 |
| Importing | Execute SFTP Download | Uses EXECUTE with WinSCP.exe to download a specified file from an SFTP server site requiring login information. | 2019-07-05 |
| Grouping | Multiple Table Comparison - Calculate Percentage Change | For each key field group in multiple tables, calculates the amount changes in the specified numeric field from table to table in order of table names. Results are expressed as total amount change, percentage amount change, transaction count change, and transaction count percentage change. | 2019-07-05 |
| Grouping | Multiple Table Comparison - Calculate Percentage Change - Single Field | For each key field in multiple tables, calculates the amount changes in the specified numeric field from table to table in order of table names. Results are expressed as total amount change, percentage amount change, transaction count change, and transaction count percentage change. | 2019-07-05 |
| Grouping | Repeating Counter | Increments a counter field by 1 until the maximum specified counter number is reached, then starts the counter over repeatedly until the last record is reached. | 2019-07-05 |
| Harmonizing | Insert character(s) after every N characters | Inserts the specified character(s) after every N characters in a text field. | 2019-06-12 |
| Harmonizing | Shorten Character Fields | Shortens all character fields in a table to the minimum required length based on each individual field’s content. | 2019-06-12 |
| Importing | Import Consolidated Canadian Autonomous Sanctions List | Uses EXECUTE to download the Consolidated Canadian Autonomous Sanctions List from the Government of Canada website and imports the data into an ACL table. | 2019-06-12 |
| Importing | OCR - Import Scanned PDF Files - English language (R package Tesseract) | Imports a scanned PDF using the R package "Tesseract" (requires prior installation). | 2019-02-13 |
| Statistics | Classified Nth Percentile | Extracts all records where the values in a given key field group are equal to or greater than the calculated cutoff value representing the Nth percentile. | 2019-01-11 |
| Statistics | Classified Nth Percentile - Single Field | Extracts all records where the values associated with a given key field value are equal to or greater than the calculated cutoff value representing the Nth percentile. | 2019-01-11 |
| Importing | Import Medi-Cal Suspended and Ineligible Providers List - VBScript | Uses EXECUTE to download and import the previous month’s Suspended and Ineligible Provider List (Medi-Cal). Parses the aliases found in field AKA_DBA_Names into individual records. | 2019-01-11 | Harmonizing | Insert Blanks Before Capitalized Letters | Inserts a blank space before each capitalized letter in a string consisting of upper and lower case letters, except the first letter. | 2019-01-11 | Harmonizing | Split On Nth Blank | Splits the text in a field into two fields, showing the text before and after the Nth blank respectively. | 2019-01-11 |
| Importing | SAP Import AP Transaction Tables - Data Connector for use with SAP ERP | Imports BKPF_BSIK and BKPF_BSASK tables from SAP for use in typical AP analysis. This script uses the Data Connector for use with SAP ERP to import the data (requires subscription). | 2018-12-18 |
| Importing | SAP Import Vendor Master Tables - Data Connector for use with SAP ERP | Imports several tables from SAP used in typical vendor analysis. This script uses the Data Connector for use with SAP ERP to import the data (requires subscription). | 2018-12-18 |
| Pre-built Robots | Pre-built Robot for SAP P2P - Data Connector for use with SAP ERP | This Pre-built Robot provides automated scripts to extract and analyze data from SAP ERP related to accounts payable transactions, user management, and vendor management. This script uses the Data Connector for use with SAP ERP to import the data (requires subscription). | 2018-12-04 |
| Importing | Downloading a File with Python | Downloads a file from any URL with Python, using the urllib library included in the Python core package. | 2018-11-27 |
| Date and Time | Collapse Overlapping Dates - Expanding End Date | For records sharing the same key fields, collapses those records whose start and end dates overlap by at least one day into a single record. The resulting start and end dates reflect the earliest and latest date found in the combined records. | 2018-11-13 |
| Date and Time | Collapse Overlapping Dates - Expanding End Date - Single Field | For records sharing the same key field, collapses those records whose start and end dates overlap by at least one day into a single record. The resulting start and end dates reflect the earliest and latest date found in the combined records. | 2018-11-13 |
| Date and Time | Collapse Overlapping Dates - Fixed End Date | For records sharing the same key fields, collapses those records whose start date falls between the start and end date of the first record in the group into a single record. | 2018-11-13 |
| Date and Time | Collapse Overlapping Dates - Fixed End Date - Single Field | For records sharing the same key field, collapses those records whose start date falls between the start and end date of the first record in the group into a single record. | 2018-11-13 |
| Importing | Import SAM - Exclusions Public Extracts - VBScript | Uses EXECUTE to download, unzip, and import the previous day's SAM Exclusions Public Extracts data from www.sam.gov. UPDATED Fixed VBScript errors that occurred due to a change in the URL of the source file. |
2018-11-13 |
| Other Utilities | ZIP Code Validation | Verifies that a given ZIP code exists within the corresponding state, based on the first 3 digits. Validates the length of each ZIP code, verifies that the ZIP code contains only numeric digits, and verifies that the state abbreviation is valid. | 2018-10-02 |
| Grouping | Difference in numeric values between earliest and latest date | For the specified key fields, calculates the difference in a numeric field between the earliest and latest available dates. | 2018-08-15 |
| Grouping | Difference in numeric values between earliest and latest date - Single Field | For the specified key field, calculates the difference in a numeric field between the earliest and latest available dates. | 2018-08-15 |
| Date and Time | Overlapping Dates - Same Table | Within a single table, this script identifies records sharing the same key field(s) that have overlapping date ranges based on a start date and an end date field. | 2018-08-15 |
| Date and Time | Overlapping Dates - Same Table - Single Field | Within a single table, this script identifies records sharing the same key field that have overlapping date ranges based on a start date and an end date field. | 2018-08-15 |
| Date and Time | Overlapping Dates - Different Tables | In two tables sharing the same key fields, this script identifies records that have overlapping date ranges based on a start date and an end date field. | 2018-08-15 |
| Date and Time | Overlapping Dates - Different Tables - Single Field | In two tables sharing the same key field, this script identifies records that have overlapping date ranges based on a start date and an end date field. | 2018-08-15 |
| Project Items | Create Alternate Column Titles | Assigns user-specified alternate column titles to the fields in a table. The alternate column titles to be used are stored in a separate ACL table. Applying alternate column titles can help clarify the content for data sources with complex technical field names. | 2018-08-15 |
| Duplicates | Eliminate Duplicated Records | Extracts only the first occurrence of records where the user-specified fields are duplicated in multiple records. Also extracts records that are not duplicated. This script may be useful if other methods, such as summarizing the data to remove duplicates, are resource intensive. | 2018-08-15 |
| Duplicates | Eliminate Duplicated Records - Single Field | Extracts only the first occurrence of records where a user-specified character field is duplicated in multiple records. Also extracts records that are not duplicated. This script may be useful if other methods, such as summarizing the data to remove duplicates, are resource intensive. | 2018-08-15 |
| Other Utilities | Geospatial Spread Between ZIP Codes | By key field and date, calculates the distance between the location of the minimum latitude/longitude and the location of the maximum latitude/longitude of all ZIP codes visited. | 2018-05-31 |
| Importing | Import Corruption Perception Index (CPI) 2017 - VBScript | Uses EXECUTE to download and import the 2017 Corruption Perception Index from Transparency.org. Cleans up the file structure and creates 2-letter and 3-letter ISO country code fields for further processing. | 2018-05-31 |
| Isolating | Change Within N Months | By key field group, reports changes that occur in a specified field within N months of each other. | 2018-01-16 |
| Isolating | Change Within N Months - Single Field | By key field, reports changes that occur in a specified field within N months of each other. | 2018-01-16 |
| Isolating | Change Within N Days | By key field group, reports changes that occur in a specified field within N days of each other. | 2018-01-16 |
| Isolating | Change Within N Days - Single Field | By key field, reports changes that occur in a specified field within N days of each other. | 2018-01-16 |
| Project Items | Delete tables Matching a Specified Naming Convention | Deletes tables that match the provided naming convention. | 2018-01-16 |
| Grouping | Collapse / Combine Records | Collapses / combines field values from multiple records sharing the same key field(s) into space separated lists in a single record. Updated Improved error handling for large tables. |
2017-12-21 |
| Grouping | Collapse / Combine Records - Single Field | Collapses / combines field values from multiple records sharing the same key field into space separated lists in a single record. Updated Improved error handling for large tables. |
2017-12-21 |
| Project Items | Verify Field Names Exist in Table | Verifies whether or not required field names exist in a user-specified file. | 2017-11-27 |
| Reordering | SAM Parse Cross Reference Entries | Parses the various names listed in the Cross_Reference field in the System for Award Management (SAM) Public Extract - Exclusions table into a single name per record. | 2017-11-27 |
| Reordering | Parse Matrix | Converts a matrix of columns and rows (such as a segregation of duties table) into records displaying the two intersecting values side by side along with the value shown at the intersection. | 2017-09-28 |
| Grouping | Create Group ID - Specified Start Value | Starting with a predetermined number, creates a unique ID for records that share the same key fields. Unlike the Create Group ID script in ScriptHub, the script accomodates a starting ID other than 1. This feature may be used to continue numbering created in a previous analysis. | 2017-09-28 |
| Grouping | Create Group ID - Specified Start Value - Single Field | Starting with a predetermined number, creates a unique ID for records that share the same key field. Unlike the Create Group ID script in ScriptHub, the script accomodates a starting ID other than 1. This feature may be used to continue numbering created in a previous analysis. | 2017-09-28 |
| Appending/Joining | Auto-Maintain Known Exceptions | Compares the records in a table to another table of previously populated exceptions, and extracts only the unmatched records to a table for review. Appends the unmatched exceptions to the exception table so that they will not be reported again for review next time they occur. | 2017-09-28 |
| Appending/Joining | Auto-Maintain Known Exceptions - Single Field | Compares the records in a table to another table of previously populated exceptions, and extracts only the unmatched records to a table for review. Appends the unmatched exceptions to the exception table so that they will not be reported again for review next time they occur. | 2017-09-28 |
| Isolating | Unchanged Values in the Last N Months | For records sharing the same key field(s), reports transactions where a specified test field value (amount) has not changed in the last N months for which transactions are available. | 2017-08-11 |
| Isolating | Unchanged Values in the Last N Months - Single Field | For records sharing the same key field, reports transactions where a specified test field value (amount) has not changed in the last N months for which transactions are available. | 2017-08-11 |
| Isolating | Unchanged Values in the Last N Months - Bucketed | For records sharing the same key field(s), reports transactions where a specified test field value (amount) has not changed in the last N months for which transactions are available. | 2017-08-11 |
| Date and Time | Elapsed Days Between The Earliest And Latest Dates | For the given key fields, calculates the number of elapsed days between the earliest and latest dates. | 2017-08-11 |
| Date and Time | Elapsed Days Between The Earliest And Latest Dates - Single Field | For the given key field, calculates the number of elapsed days between the earliest and latest dates. | 2017-08-11 |
| Date and Time | Elapsed Days Between The Latest Date And Prior Date | For the given key fields, identifies the number of elapsed days between the latest date and the prior date. | 2017-08-11 |
| Date and Time | Elapsed Days Between The Latest Date And Prior Date - Single Field | For the given key field, identifies the number of elapsed days between the latest date and the prior date. | 2017-08-11 |
| Date and Time | Count of Specific Weekday by Month | This script calculates how many times a given day of the week occurs in each month. | 2017-07-31 |
| Project Items | Rename Fields - Table | Renames the field in a table to the field names specified in another table. | 2017-07-31 |
| Project Items | Rename Fields - Variable | Renames the field in a table to the field names specified in a variable. | 2017-07-31 |
| Reordering | Random Numbers with Minimum and Maximum | In each record, creates a field containing a random number between a specified minimum and maximum value (inclusive). | 2017-07-31 |
| Isolating | Numeric Fields containing 0 | Extracts records where N fields in a set of numeric fields are 0. | 2017-07-31 |
| Isolating | Back To Back Transactions | Reports transactions that share the same key fields and occur on two or more successive days. Updated Fixed incorrect reference to an input variable name. |
2017-07-31 |
| Isolating | Back To Back Transactions - Single Field | Reports transactions that share the same key field and occur on two or more successive days. Updated Fixed incorrect reference to an input variable name. |
2017-07-31 |
| Importing | Import Consolidated List of Targets - United Kingdom | Uses EXECUTE to download the Consolidated List of Targets from the UK HM Treasury website and imports the data into an ACL table. Updated Fixed incorrect qualifier in IMPORT command. |
2017-07-31 |
| Importing | Import Corruption Perception Index (CPI) 2016 - VBScript | Uses EXECUTE to download and import the 2016 Corruption Perception Index from Transparency.org. Cleans up the file structure and creates 2-letter and 3-letter ISO country code fields for further processing. | 2017-04-18 |
| Project Items | Table Statistics | Creates an ACL table with a list of all tables contained in the project, along other general information such as source file path, file size, record count, field count, and record length. | 2017-04-18 |
| Project Items | Delete Orphan Tables | Deletes table layouts where the associated data file cannot be found. | 2017-04-18 |
| Harmonizing | Standardized Address - Method 2 | Standardizes a single address field by replacing common address terms such as Av, Ave, and Avenue with a common abbreviation. UPDATED Improved handling of external file Common_Address_Terms.csv. |
2017-04-18 |
| Totals | Text occurs in N Fields | Creates a computed field counting the number of fields a specified text appears in. | 2017-02-23 |
| Reordering | Sort Characters In Field | Reorders the alphanumeric characters in a specified field in alphabetic order, which can help with identifying possible transposed characters. RENAMED This script was renamed from Identify Transposed Characters to Sort Characters in Field to better describe the functionality of this script. |
2017-02-23 |
| Harmonizing | Convert Alternate Column Titles to Field Names | Creates a table that uses alternative column names as field names. | 2017-02-20 |
| Duplicates | Repeat Record N Times | Repeats a record as many times as specified in a numeric counter field. | 2017-02-16 |
| Importing | Import HighBond Tables (Projects) | Imports Projects tables from HighBond. Joins the imported tables to produce prepared tables that can be used for further analysis. | 2017-02-16 |
| Grouping | Static Group ID | Creates a unique ID (‘1’ to ‘N’) for records following a specified static value, incrementing the ID by 1 every time the specified value is encountered. | 2017-01-19 |
| Reordering | Numeric Column Sort | Creates a computed field that lists numeric values in consecutive columns in ascending or descending order, separated by a space. | 2017-01-19 |
| Isolating | Nth Consecutive Record | Reports every Nth record in a group of records where a numeric value, date, or text string appears in consecutive records that share the same key fields. | 2016-12-14 |
| Isolating | Nth Consecutive Record - Single Field | Reports every Nth record in a group of records where a numeric value, date, or text string appears in consecutive records that share the same key field. | 2016-12-14 |
| Isolating | Value Occurs in N Consecutive Records | Reports records where a numeric value, a date, or a text string appears in N consecutive records that share the same key fields. | 2016-12-14 |
| Isolating | Value Occurs in N Consecutive Records - Single Field | Reports records where a numeric value, a date, or a text string appears in N consecutive records that share the same key field. | 2016-12-14 |
| Data Quality | USA Social Security Number (SSN) Validation | For United States only. Validates the digits in a US social security number against specified rules. UPDATED Fixed an issue with field names. |
2016-12-14 |
| Isolating | Condition occurs in N Fields | Extracts records where the same characteristic occurs N times across multiple fields (see Details page for examples). | 2016-12-05 |
| Grouping | Table Comparison - Calculate Percentage Change - Single Field | For each key field in two tables, calculates the percentage change and amount change between a specified numeric field in both tables. | 2016-12-05 |
| Totals | Classified Running Total with Starting Balance | Creates a running total for a single numeric field, restarting at every change in the specified key fields, using a user-specified starting balance for the key field group. | 2016-11-18 |
| Totals | Classified Running Total with Starting Balance - Single Field | Creates a running total for a single numeric field, restarting at every change in a single specified key field, using a user-specified starting balance for each key field value. | 2016-11-18 |
| Totals | Running Total with Starting Balance | Creates a Running Total field for a single numeric field, based on a user-specified starting balance. | 2016-11-18 |
| Grouping | Table Comparison - Calculate Percentage Change | For each key field group in two tables, calculates the percentage change and amount change between a specified numeric field in both tables. | 2016-11-18 |
| Grouping | Trending - Day, Month, Quarter, or Year | Calculates total amounts and counts grouped by one or more key fields, for successive date ranges (days, months, quarters, or years) that can be used for trending or reporting purposes such as time series charts. | 2016-11-14 |
| Grouping | Trending - Day, Month, Quarter, or Year - Single Field | Calculates total amounts and counts grouped by a single key field, for successive date ranges (days, months, quarters, or years) that can be used trending or reporting purposes such as time series charts. | 2016-11-14 |
| Exporting/Reporting | Select Export to Excel or Delimited (254 characters) | Exports fields in a specified table to XLSX or CSV format, depending on the presence of fields exceeding 254 characters in length. | 2016-11-14 |
| Harmonizing | Standardize Corporate Names | Removes common company entity names from a company name field. Updated Replaces Remove Company Suffix. Added new entity names. Revised the approach for improved performance. |
2018-08-15 |
| Date and Time | Date Dimensions | Calculates several date dimensions for a user specified date range. | 2016-10-27 |
| Data Quality | Empty Fields - Method 2 | Creates a table with a list of fields that are entirely empty (character, numeric, or date) and extracts non-empty fields to a new output table (optional). | 2016-10-27 |
| Date and Time | Week Numbers | Calculates the week number of every day in a user defined year or years. UPDATED Added unique week ID for every week in the date range. |
2016-10-27 |
| Other Utilities | Unzip Multiple Files | Uses the 7zip application to extract contents of a user specified and password protected ZIP file to the zipped file's location. | 2016-10-27 |
| Isolating | Split Transactions - Fixed Limit - Within 24 hours | Identifies possible split transactions to circumvent a fixed threshold. Reports transactions with duplicated key fields that occur within 24 hours of each other and in total exceed the specified limit. | 2016-10-07 |
| Isolating | Earliest Record By Date | In a group of records sharing the same key fields, retains the records with the earliest date in a user-specified date field. | 2016-10-07 |
| Isolating | Earliest Record By Date - Single Field | For all unique values in a named key field, retains the records with the earliest date in a user-specified date field. | 2016-10-07 |
| Isolating | Latest Record By Date | In a group of records sharing the same key fields, retains the records with the latest date in a user-specified date field. | 2016-10-07 |
| Isolating | Latest Record By Date - Single Field | For all unique values in a named key field, retains the records with the latest date in a user-specified date field. | 2016-10-07 |
| Other Utilities | Create Table Common Address Terms | Supporting script for Address Standardization. Imports Common_Addr_Terms.csv. Contains a list of frequently used address terms and a standardized abbreviation for similar terms. UPDATED Removed duplicated and / or conflicting address abbreviations and labels. Added new abbreviations. |
2016-10-07 |
| Harmonizing | Standardized Address - Method 1 | Standardizes a single address field by replacing common address terms such as Av, Ave, and Avenue with a common abbreviation. UPDATED Removed duplicated and / or conflicting address abbreviations and labels. Added new abbreviations. |
2016-10-07 |
| Isolating | Unexpected Activity - Within N Days after Start Date | Compares dates in a transaction table to start dates in a master table, and reports any transactions that have occurred within N days after the start date. | 2016-10-07 |
| Isolating | Unexpected Activity - Within N Days after Start Date - Single Field | Compares dates in a transaction table to start dates in a master table, and reports any transactions that have occurred within N days after the start date. Transaction and Master tables can be joined on a single key field. | 2016-10-07 |
| Grouping | Classified Top N Count | Identifies top N unique items within user-specified key field groups, by count. | 2016-08-25 |
| Grouping | Classified Top N Count - Single Field | Identifies top N unique items within a single user-specified key field, by count. | 2016-08-25 |
| Isolating | Unexpected Activity - After Cut-off Date | Compares dates in a transaction table to cut-off dates in a master table, and reports any transactions that have occurred after the cut-off date. Transaction and master tables can be joined on multiple key fields. | 2016-08-25 |
| Isolating | Unexpected Activity - After Cut-off Date - Single Field | Compares dates in a transaction table to cut-off dates in a master table, and reports any transactions that have occurred after the cut-off date. Transaction and master tables can be joined on a single key field. | 2016-08-25 |
| Isolating | Unexpected Activity - Before Start Date | Compares dates in a transaction table to start dates in a master table, and reports any transactions that have occurred before the start date. Transaction and master tables can be joined on multiple key fields. | 2016-08-25 |
| Isolating | Unexpected Activity - Before Start Date - Single Field | Compares dates in a transaction table to start dates in a master table, and reports any transactions that have occurred before the start date. Transaction and master tables can be joined a single key field. | 2016-08-25 |
| Keyword Searching | Unmatched Pairings | Reports records sharing the same key fields where a specified word or phrase appears in one record, but a corresponding word or phrase does not appear in the same field in another record. | 2016-08-11 |
| Analysis - Vendor Master | Vendor Master Module - High Risk - CPI Countries - 2015 | Reports AP transactions for vendors whose country code in the Vendor Master matches a country code in the Corruption Index Perception list, where the CPI score is below a specified maximum CPI score. | 2016-08-11 |
| Importing | Import Corruption Perception Index (CPI) 2015 - VBScript | Uses EXECUTE to download and import the 2015 Corruption Perception Index from Transparency.org. Cleans up the file structure and creates 2-letter and 3-letter ISO country code fields for further processing. | 2016-08-11 |
| Keyword Searching | Text Analysis - All Words | Lists all words appearing in a specified field in separate records, and the count and percentage occurrence of the first letter, first two letters, last letter, and last two letters. | 2016-06-22 |
| Statistics | Benford - Last 2 Digits | Simulates a Benford calculation on the last two digits of a specified numeric field. | 2016-06-16 |
| Date and Time | Age in Years, Months, and Days | Calculates age in years, months, and days. | 2016-06-16 |
| Isolating | Sudden Activity | Identifies activity after N days of inactivity. | 2016-06-16 |
| Date and Time | Weekend and Holiday Transactions | Identifies transactions occurring on weekends or holidays. | 2016-06-16 |
| Analysis - Vendor Master | Vendor Master Module - Vendor - Employee Match | Identifies potential Vendor-Employee matches where one or more of the following data elements are identical or similar: Name, Initials, Address, Phone Number, Tax ID, or Bank Account Number | 2016-06-16 |
| Other Utilities | Active Directory - Changes to Critical Security Groups | Uses EXECUTE to import a list of members of one or more AD security groups. Compares the member list to that of a previous version, if available, to identify any additions or removals, for further investigation. | 2016-06-06 |
| Isolating | Transaction Amounts Near Threshold | Identifies transaction amounts that are within N percent below or above a specified threshold. | 2016-06-06 |
| Isolating | Transaction Amounts Below Threshold | Identifies transaction amounts that are within N percent below a specified threshold. | 2016-06-06 |
| Isolating | Transaction Amounts Above Threshold | Identifies transaction amounts that are within N percent above a specified threshold. | 2016-06-06 |
| Date and Time | Transactions Within N Seconds | Reports transactions sharing the same key fields that occur within N seconds of each other. | 2016-05-11 |
| Date and Time | Transactions Within N Seconds - Method 2 | Reports transactions that occur within N seconds of a previous transaction sharing the same key fields. | 2016-05-11 |
| Project Items | Create Table List - Unicode | Creates an ACL table with a list of all table layouts contained in the ACL project at run time. | 2016-05-11 |
| Data Quality | Keyfield Profiling - Single Field - Method 2 | Creates summary information for records sharing the same single key field, such as totals, minimum, maximum, and average values for all numeric fields, and minimum and maximum dates for all date fields. | 2016-05-11 |
| Data Quality | Keyfield Profiling - Method 2 | Creates summary information for records sharing the same key field(s), such as totals, minimum, maximum, and average values for all numeric fields, and minimum and maximum dates for all date fields. | 2016-05-11 |
| Data Quality | Keyfield Profiling - Single Field | Creates a record count for a user specified key field, the percentage contribution of the key field value to the full population, and a running total of the percentage contribution. | 2016-05-11 |
| Other Utilities | Active Directory - Last Logon vs Last Password Reset | Compares recent last logon dates to last password reset dates to help identify users still active in the system that may need to be deactivated. | 2016-05-09 |
| Isolating | Differences In Records - Single Field | Reports records where at least one non-key field is different for records sharing the same key field. This script can be used to quickly find differences in tables such as vendor master changes (before and after). | 2016-05-09 |
| Date and Time | Classified Custom Aging - Single Field | Assigns transactions into user defined aging buckets based on start and end date fields, by a user defined key field, and calculates the subtotal of a user specified numeric field in each bucket. | 2016-05-09 |
| Parsing | Create Many Files - Single Fields | Extracts records from an existing table to a new table at every change in the value of the key field. | 2016-05-09 |
| Appending/Joining | One To One Join - Single Field | Performs a one-to-one join on a single user specified key field. Reports matched records between the two tables. Unmatched records are stored in separate tables. | 2016-04-18 |
| Appending/Joining | One To One Join | Performs a one-to-one join on user specified key fields. Reports matched records between the two tables. Unmatched records are stored in separate tables. | 2016-04-18 |
| Grouping | Classified Top N Values - Single Field | Selects records containing the top N distinct values from within each user specified key field. | 2016-04-18 |
| Grouping | Classified Top N Values | Selects records containing the top N distinct values from within each user specified key field group. | 2016-04-18 |
| Grouping | Create Group Record Counter - Single Field | Creates an ascending line number counter for records sharing the same key field. The counter resets for each new key field value. | 2016-04-18 |
| Grouping | Create Group ID - Single Field | Creates a unique ID (‘1’ to ‘N’) for records that share the same key field. | 2016-04-18 |
| Sequential | Count Sequential Days - Single Field | Creates start_date and end_date fields for sequential date ranges sharing the same key field. Counts the number of sequential dates. | 2016-04-18 |
| Data Quality | Exact Table Match | Compares the table layout and data of two tables in several ways to determine if the tables are identical. Reports “Pass” or “Fail” results for each of the tests. | 2016-04-18 |
| Patterns / Transpositions | List to Records | Creates one record for every value in a list. | 2016-04-06 |
| Sampling | Clustered Sampling on Count - Single Field | Simulates a cluster sampling technique to randomly select a cluster and pull the records from that cluster up to the sample size. Selects additional random clusters if the record count of the original cluster is smaller than the sample size. A cluster is defined based on the values in a single key field. | 2016-04-06 |
| Sampling | Clustered Sampling on Amount - Single Field | Simulates a cluster sampling technique to randomly select a cluster and pull the records from that cluster up to the sample amount. Selects additional random clusters if the total amount of the original cluster is smaller than the sample amount. | 2016-04-06 |
| Statistics | Classified Standard Deviation - Single Field | Simulates calculation of Standard Deviations (1,2,3) and other statistical output grouped by a user specified key field. | 2016-04-06 |
| Totals | Classified Running Total - Repeating Values - Single Field | Creates a running total for a single numeric field, restarting at every change in the specified key field. If a numeric value is repeated in successive rows within the same key field group, it will only be added once. | 2016-04-06 |
| Totals | Classified Running Total - Single Field | Creates a running total for a single numeric field, restarting at every change in a single specified key field. | 2016-04-06 |
| Sampling | Classified Random Record Sampling - Single Field | Simulates a random record sample taken from each group of records sharing the same single key field. | 2016-04-06 |
| Statistics | Classified Benford - Single Field | Simulates a Benford calculation for each distinct value in a user-specified key field. | 2016-04-06 |
| Date and Time | Get Next Workday | Determines the next work day for the dates in a DATETIME field, allowing for weekends and holidays. | 2016-03-21 |
| Importing | Delete Header Records in Excel | Deletes the specified number of header records in one or more worksheets in Excel files found in a specified directory. | 2016-03-14 |
| Grouping | Same Same Different Different Method 1 | Reports records that share the same key fields and none of the different fields contain duplicate values in the same field on other records within the group. | 2016-03-14 |
| Exporting/Reporting | Apply Numeric Formatting | Applies the user specified formatting to all applicable numeric fields in a table. | 2016-02-25 |
| Date and Time | Elapsed Years | Calculates the number of fully elapsed years between a start date and a user specified cut-off date, or the current run date. | 2016-02-25 |
| Harmonizing | Standardize Name - First, Middle, Last | Standardizes a full name field by breaking out first, middle initial, and last name fields | 2016-02-25 |
| Harmonizing | Company and Name Matching | Compares two tables on first name, last name, and company name to find exact and near matches. | 2016-02-11 |
| Patterns/Transpositions | Isolate PO Box Addresses | Isolates records where common variations of PO Box are found in a specified field. | 2016-02-11 |
| Grouping | Same Same Different | Identifies near duplicate records by reporting instances of same values in one or more key fields while requiring the value in another key field to be different. | 2016-02-11 |
| Exporting/Reporting | Static Field | Fills blanks in a user specified field with the value immediately above the blanks. | 2016-02-11 |
| Date and Time | Timestamp - Calculate Elapsed Seconds | Examines timestamps for missing day, hour, minute, or seconds components, substitutes 00 for missing components, and calculates elapsed time in seconds. | 2016-02-11 |
| Appending/Joining | Append Multiple Tables | Appends multiple tables contained in the same project. | 2016-01-29 |
| Keyword Searching | FCPA - Phrase Matching | Reports records where a specified field contains one or more words in a given phrase or combination of words, prioritized in descending order by closeness of the match. | 2016-01-29 |
| Importing | Import and Append All Files | Imports and appends all files contained in a user specified directory and its subdirectories, using an IMPORT command provided by the user. | 2016-01-29 |
| Date and Time | Add Elapsed Seconds | Adds elapsed seconds to a start date and time. | 2016-01-15 |
| Importing | Import OIG LEIE - CSV - List of Excluded Individuals / Entities - VBScript | Uses EXECUTE to download the OIG LEIE data in CSV format. Updated Revised the name of the csv file being imported. |
2018-08-15 |
| Data Quality | Count empty values | Counts the number of fields that do not have a value in a given record. | 2015-12-03 |
| Data Quality | Count non-empty values | Counts the number of fields that have a value in a given record. | 2015-12-03 |
| Patterns/Transpositions | Isolate strings matching a pattern | Isolates strings in a longer character field that match a specified pattern. | 2015-12-03 |
| Duplicates | Near Duplicates | Uses DICECOEFFICIENT() to identify potential duplicate records based on similar or identical strings. | 2015-12-03 |
| Pre-built Robots | Pre-built Robot for SAP P2P - Direct Link | This Pre-built Robot provides automated scripts to extract and analyze data from SAP ERP related to accounts payable transactions, user management, and vendor management. This script uses Direct Link to import the data (requires subscription). | 2015-11-23 |
| Appending/Joining | Join on Nearest Date - Past or Future | Joins a date field in the primary table with the same or closest available date in the secondary table. The closest available date may be a future or a past date. | 2015-11-12 |
| Appending/Joining | Join on Nearest Future Date | Joins a date field in the primary table with the same or nearest future date in the secondary table. | 2015-11-12 |
| Appending/Joining | Join on Nearest Past Date | Joins a date field in the primary table with the same or nearest past date in the secondary table. | 2015-11-12 |
| Date and Time | Months between Dates | Calculates the number of full and / or partial months between two dates. | 2015-11-12 |
| Removing | Truncate Without Rounding | Truncates the values in a numeric field at the specified number of decimals, without rounding. | 2015-11-12 |
| Date and Time | Add Working Days | Adds the specified number of working or business days to a start date. | 2015-11-02 |
| Date and Time | Random Non-Working Days | Creates a table of N random non-working or non-business days within a user entered date range (inclusive). | 2015-11-02 |
| Date and Time | Random Working Days | Creates a table of N random working or business days within a user entered date range (inclusive). | 2015-11-02 |
| Project Items | Create Table and Field List | Creates an ACL table with a list of all table layouts contained in the ACL project at run time, as well as their field names and other metadata. | 2015-08-31 |
| Data Quality | Minimally Populated Fields | Identifies and removes minimally populated fields in a table. | 2015-08-31 |
| Date and Time | T&E Weekend and Holiday Transactions | Identifies T&E transactions entered on unusual days such as weekends and holidays. | 2015-08-31 |
| Analysis - Accounts Payable | AP Weekend and Holiday Transactions | Identifies transactions entered on unusual days such as weekends and holidays. | 2015-07-09 |
| Data Quality | Luhn Algorithm (Modulus 10) | Performs number validation according to the Luhn algorithm, also known as Modulus 10. | 2015-07-09 |
| Other Utilities | Monitor Disk Space | Uses EXECUTE with the WMIC command line utility to query the available free space and total capacity on accessible storage devices. | 2015-07-09 |
| Analysis - Vendor Master | Vendor Master Module - Dormant Vendors | Identifies vendors who have had no AP transactions in the last N days. | 2015-07-09 |
| Analysis - Vendor Master | Vendor Master Module - Foreign Bank Accounts | Identifies vendor master records where the bank account country is different from the vendor country. | 2015-07-09 |
| Analysis - Accounts Payable | AP Duplicate Invoices - Same Amount Same Date Different Vendor | Identifies possible duplicate invoices for the same amount and same date but a different vendor. | 2015-06-30 |
| Analysis - Accounts Payable | AP Duplicate Invoices - Same Vendor Amount Date Different Vendor Invoice Number Pattern | Identifies possible duplicate invoices with the same vendor, same amount, same date, and different vendor invoice number patterns. | 2015-06-30 |
| Analysis - Accounts Payable | AP Unusual Invoice Numbers | Identifies invoice number patterns that differ from the commonly used invoice number pattern for a given vendor. | 2015-06-30 |
| Harmonizing | Standardize Name - Nicknames | Standardizes a single name field by replacing nicknames with the commonly associated full first names. This script may help identify potential duplicate names such as Bill Smith and William Smith. | 2015-06-30 |
| Analysis - Accounts Payable | AP Duplicate Invoices - Same Vendor Same Amount Different Date | Identifies possible duplicate invoices with the same vendor, same amount, same vendor invoice number, and different vendor invoice date. | 2015-06-04 |
| Analysis - Accounts Payable | AP Duplicate Invoices - Same Vendor Same Amount Different Vendor Invoice Number | Identifies possible duplicate invoices with the same vendor, same amount, and different vendor invoice numbers. | 2015-06-04 |
| Analysis - Accounts Payable | AP Duplicate Invoices - Same Vendor Same Amount Similar Date | Identifies possible duplicate invoices from the same vendor and for the same amount where the vendor invoice dates are within N days of each other. | 2015-06-04 |
| Analysis - Accounts Payable | AP Duplicate Invoices - Same Vendor Similar Amount | Identifies possible duplicate invoices from the same vendor where the amount is within a percentage defined by the user. | 2015-06-04 |
| Project Items | Rename Fields - Reserved Words (Non-Unicode) | Replaces reserved keywords in field names with substitute names, allowing the table to be opened. Stores the revised table layout in a copy of the original project. | 2015-05-26 |
| Isolating | Track Changes | Tracks changes between current and previous versions of the same table, occurring in specified test fields that share the same key field(s). | 2015-05-26 |
| Analysis - Vendor Master | Vendor Master Module - Frequent Changes to Vendor Master | Tests for instances where critical elements of a vendor master record are changed frequently in order to redirect an authorized payment. Management should be notified when there are frequent or unexpected changes to a vendor master record. Management should review and remediate on a timely basis. | 2015-05-26 |
| Sequential | List Non Sequential Documents | Displays documents belonging to the same key field group where a document number is not in numeric sequence with at least one other document number (higher or lower). | 2015-05-11 |
| Date and Time | Standardize Date Formats | Standardizes dates of varying formats into a common date format. | 2015-05-05 |
| Data Quality | Drop Empty Fields | Extracts all fields from a table that are not entirely empty (character or date), or 0 for numeric. Creates a second table showing the names of fields that were not included in the extract. | 2015-04-28 |
| Keyword Searching | Word Permutations | Creates all possible permutations of the words in a single text string. | 2015-04-28 |
| Harmonizing | Standardize Name - Prefix and Suffix | Standardizes a single name field by removing common prefixes and suffixes (also known as post-nominal letters), such as Dr, PhD, Atty, and so on. | 2015-04-13 |
| Reordering | Crosstab - Sequential Dates | Sorts the output of the CROSSTAB command in ascending or descending order on the dates shown in the column names. | 2015-04-01 |
| Harmonizing | Standardize Text - NYSIIS | Standardizes a single text field by substituting phonetically similar characters with placeholder characters. | 2015-04-01 |
| Sampling | Validate Sample | Reports the record counts for each key field group before and after taking a sample. | 2015-04-01 |
| Importing | SAP Import AP Transaction Tables | Imports several tables from SAP used for constructing Invoices Received tables, as per Common Data Model. | 2015-03-09 |
| Importing | SAP Import Vendor Master Tables | Imports several tables from SAP used for constructing Vendor Master tables, as per Common Data Model. | 2015-03-09 |
| Importing | SAP Job Completion Check | Repeatedly checks the status of SAP background jobs up to N times, at user specified intervals. If all background jobs are completed, retrieves all tables. If after N attempts some background jobs are not completed, the script errors out. Updated Revised the approach to improve reliability. |
2018-08-15 |
| Importing | SAP Prepare AP Transaction Tables | Prepares the SAP tables imported by the SAP Import AP Transaction Tables script, by joining related tables, and migrating the data fields to the Common Data Model names. | 2015-03-09 |
| Importing | SAP Prepare Vendor Master Tables | Prepares the SAP tables imported by the SAP Import Vendor Master Tables script, by joining related tables, and migrating the data fields to the Common Data Model names. | 2015-03-09 |
| Analysis - Vendor Master | Vendor Master Module - Duplicate Address | Reports possible duplicate AP transactions based on duplicated addresses for different vendors in the Vendor Master table. | 2015-03-09 |
| Analysis - Vendor Master | Vendor Master Module - Duplicate Bank Accounts | Reports possible duplicate AP transactions based on duplicated bank accounts for different vendors in the Vendor Master table. | 2015-03-09 |
| Analysis - Vendor Master | Vendor Master Module - Duplicate Tax ID | Reports possible duplicate AP transactions based on duplicated tax IDs for different vendors in the Vendor Master table. | 2015-03-09 |
| Analysis - Vendor Master | Vendor Master Module - High Risk - CPI Countries | Reports AP transactions for vendors whose country code in the Vendor Master matches a country code in the Corruption Index Perception list, where the CPI score is below a specified maximum CPI score. | 2015-03-09 |
| Analysis - Vendor Master | Vendor Master Module - New Vendors | Reports transactions exceeding specified thresholds for new vendors created during the current investigation period. | 2015-03-09 |
| Analysis - Vendor Master | Vendor Master Module - SAM List | Reports transactions for vendors whose name is matched in the SAM List file published on www.sam.gov. | 2015-03-09 |
| Totals | Classified Running Total - Repeating Values | Similar to the original Classified Running Total script, this script creates a Running Total field, but for unique values only. If a numeric value is repeated in successive rows, it will only be added once. Repeating values that appear on non-successive rows will be added each time they occur. Running totals restart at every change in the key fields | 2015-01-26 |
| Totals | Running Total - Repeating Values | Similar to the original Running Total script, this script creates a Running Total field, but for unique values only. If a numeric value is repeated in successive rows, it will only be added once. Repeating values that appear on non-successive rows will be added each time they occur. | 2015-01-26 |
| Statistics | Classified Standard Deviation | Simulates calculation of Standard Deviations (1,2,3) and other statistical output grouped by user specified key field(s). | 2014-12-22 |
| Importing | Import Corruption Perception Index (CPI) 2014 - VBScript | Uses EXECUTE to download and import the 2014 Corruption Perception Index from Transparency.org. Cleans up the file structure and creates 2-letter and 3-letter ISO country code fields for further processing. | 2014-12-22 |
| Date and Time | Create Table of Canadian Federal Holidays | Creates a table showing the actual date, and the observed date where applicable, of federal and civic Canadian holidays for the current year plus N previous and future years. | 2014-12-15 |
| Date and Time | Create Table of German Federal Holidays | Creates a table showing the actual date of federal German holidays for the current year plus N previous and future years. | 2014-12-15 |
| Patterns/Transpositions | Replace Nth Occurrence of a Text String | Creates a computed field that replaces the Nth occurrence of a user-specified text string within a character field with other specified characters. | 2014-12-15 |
| Harmonizing | Standardize North American Phone Numbers | For phone numbers in the North American Numbering Plan (NANP) only. Standardizes phone numbers following common patterns into format (999) 999-9999 and validates the resulting number against NANP rules. | 2014-12-15 |
| Date and Time | Create Table of USA Federal Holidays | Creates a table showing the actual date, and the observed date where applicable, of 10 federal US holidays for the current year plus N previous and future years. | 2014-12-05 |
| Keyword Searching | Keyword Search - Include - Multiple Fields | Reports records where a distinct word from a keyword table is found in one or more specified fields. | 2014-12-05 |
| Project Items | Convert Scripts to ACLScript | Creates an external aclscript file for every script in the ACL project, and replicates the project folder structure in the target directory. | 2014-11-26 |
| Exporting/Reporting | Export to Excel by Key Fields - Separate Workbooks | Groups records by key fields and exports the detail records for each key field group to separate Excel workbooks. | 2014-11-26 |
| Exporting/Reporting | Export to Excel by Key Fields - Separate Worksheets | Groups records by key fields and exports the detail records for each key field group to separate Excel worksheets. | 2014-11-26 |
| Importing | Import ACLScript Files | Imports external aclscript files into the project, ready to be run as internal scripts. | 2014-11-26 |
| Importing | Reuse Import Excel Command | Imports Excel files in a specified folder using an existing IMPORT EXCEL command. If required, the script removes the first N rows, as specified by the user, such as report header rows. | 2014-11-26 |
| Duplicates | Acronym Matching | Searches a specified field for duplicates between actual acronyms and computed acronyms (first letter of each word) | 2014-11-07 |
| Project Items | Add Table Name to Fields | Adds the initials of a table name to each field within the table. This process may help with keeping track of which table a field came from if multiple tables are joined. | 2014-11-07 |
| Project Items | Convert Record to Fieldnames | Identifies the first record in a table where none of the fields are blank. Converts that record to field names and uses all subsequent records as data. Removes any records that appear before that record. A suitable application for this script may be a report that was imported into Analytics with its header records still in place. | 2014-11-07 |
| Isolating | Differences In Records | Reports records where at least one non-key field is different in a group of records sharing the same key. This script can be used to quickly find differences in records that are expected to be same. Replaces the need to scroll through the record. | 2014-11-07 |
| Keyword Searching | Locate Words in Record | Identifies character fields that contain specified words in at least one record. | 2014-11-07 |
| Data Quality | NZ IRD Number Validation | For New Zealand only. Identifies IRD Numbers that appear to be invalid. | 2014-11-07 |
| Removing | PAN Truncation | Obfuscates a field by replacing the leading digits with *, displaying only the last N digits unaltered. | 2014-11-07 |
| Parsing | Parse Delimited Field | Creates a computed field for each segment of a user defined character field, based on a user defined delimiter. | 2014-11-07 |
| Appending/Joining | Primary Many to Many Join | Simulates a Primary Many to Many join, which includes unmatched records from the primary table in the results. | 2014-11-07 |
| Keyword Searching | Prioritized Search | Reports records that contain one or more words in a search string, prioritized in descending order by closeness of the match. | 2014-11-07 |
| Repeating Commands | Process Multiple Tables | Performs the same user-specified command(s) in multiple tables. | 2014-11-07 |
| Sampling | Proportional Record Sampling | Simulates proportional record sampling by selecting a specified sample size proportionally from key field groups, based on their contribution to the total amount. | 2014-11-07 |
| Importing | Reuse Import Command | Imports files in a specified folder using an existing IMPORT PRINT or IMPORT DELIMITED command. | 2014-11-07 |
| Data Quality | Summary Report | Creates a table containing the record counts and control totals for the numeric and datetime fields in one or more tables. | 2014-11-07 |
| Date and Time | Add Months To Date (Next Valid Date) | Adds a specified number of months to a date field and returns the next valid date if the original calculation returns an invalid date. | 2014-09-01 |
| Appending/Joining | Append Tables - Mismatched Layouts | Appends multiple ACL tables that contain the same type of data but may have different record structures. | 2014-09-01 |
| Appending/Joining | Append To History | Appends records to an existing table with an identical structure. | 2014-09-01 |
| Isolating | Average Exceeds Threshold | Groups records on user defined key fields and calculates a subtotal, record count, and the average, such as average business meal expense per attendee. Reports average amounts exceeding a user specified threshold. | 2014-09-01 |
| Other Utilities | Calculate Distance between Zip Codes | Calculates point-to-point distances between zip codes based on the Great Circle Distance formula. | 2014-09-01 |
| Statistics | Calculate Percentile (All Fields) | Calculates the corresponding percentile of the values in all numeric fields | 2014-09-01 |
| Statistics | Calculate Percentile (Single Field) | Calculates the corresponding percentile of every value in a user-specified test field. | 2014-09-01 |
| Other Utilities | Calculate Space Usage | Calculates the difference between a target directory's total space and actual file space used. | 2014-09-01 |
| Other Utilities | Calculate Starting Balance | Creates a table with a starting balance field by key field(s) for every month in a transactional data set. | 2014-09-01 |
| Date and Time | Calculate Working Days | Calculates the number of working days between two date fields, by removing weekend days and user defined holidays. | 2014-09-01 |
| Data Quality | Canadian SIN Validation | For Canada only. Uses Luhn's Algorithm: Each digit of the SIN is multiplied by a corresponding checksum digit (121212121). The sum of the product of this multiplication must be evenly divisible by 10. If it is not, then the SIN is invalid. | 2014-09-01 |
| Removing | Cancel Matching Amounts | Creates an ACL table showing records for which no canceling positive and negative amounts were found for the same key field(s). | 2014-09-01 |
| Statistics | Classified Benford | Simulates a Benford calculation by user specified key fields. | 2014-09-01 |
| Date and Time | Classified Custom Aging | Assigns transactions into user defined aging buckets based on start and end date fields, by key field group, and calculates the subtotal of a user specified numeric field in each bucket. | 2014-09-01 |
| Sampling | Classified Random Record Sampling | Simulates a random record sample taken from group of records sharing the same key field(s). | 2014-09-01 |
| Totals | Classified Running Total | Creates a running total for a single numeric field, restarting at every change in user specified key fields. | 2014-09-01 |
| Totals | Classified Running Total - Multiple Fields | Creates a running total for one or more numeric fields, restarting at every change in the specified key fields. | 2014-09-01 |
| Statistics | Classified Statistics | Simulates the output of STATISTICS for a single numeric or datetime field, grouped by user specified key field(s). | 2014-09-01 |
| Harmonizing | Clean Middle Initial | Removes a single character middle initial from a user specified name field. | 2014-09-01 |
| Sampling | Clustered Sampling on Amount | Simulates a cluster sampling technique to randomly select a cluster and pull the records from that cluster up to the sample amount. | 2014-09-01 |
| Sampling | Clustered Sampling on Count | Simulates a cluster sampling technique to randomly select a cluster and pull the records from that cluster up to the sample size. | 2014-09-01 |
| Other Utilities | Convert Unicode To ASCII (DOS) | Uses DOS commands via EXECUTE to convert an external UNICODE file to ASCII | 2014-09-01 |
| Other Utilities | Convert Unicode To ASCII (Powershell) | Uses Windows PowerShell and EXECUTE to convert an external UNICODE file to ASCII. | 2014-09-01 |
| Project Items | Copy and Link to Table Layout | Replicates an existing table layout for every file in a specified folder. | 2014-09-01 |
| Sequential | Count Sequential Days | Creates start_date and end_date fields for sequential date ranges sharing the same key fields. Counts the number of sequential dates. | 2014-09-01 |
| Exporting/Reporting | Create ACL View from Excel | Generates ACL table views based on parameter input imported from an external Excel parameter file. | 2014-09-01 |
| Grouping | Create Group ID | Creates a unique ID ('1' to N) for records that share the same key fields. | 2014-09-01 |
| Grouping | Create Group Record Counter | Creates an ascending line number counter for records sharing the same key fields. The counter resets for each new set of key fields. | 2014-09-01 |
| Parsing | Create Many Files | Extracts records from an existing table to a new table at every change in a group of key fields. | 2014-09-01 |
| Project Items | Create Metadata with Keyfield | Supporting script for repetitive analysis of left most key fields. Analyzes table metadata to identify the key fields to group by, based on the number of left-most fields provided by the user. | 2014-09-01 |
| Other Utilities | Create Stub | Creates table T_Stub with one record and a single field. | 2014-09-01 |
| Project Items | Create Table List | Creates an ACL table with a list of all table layouts contained in the ACL project at run time. | 2014-09-01 |
| Reordering | Crosstab - Descending Sort | Sorts the output of the CROSSTAB command in descending order on the names of the column fields. | 2014-09-01 |
| Date and Time | Custom Aging | Assigns transactions into user defined aging buckets based on start and end date fields, and calculates the subtotal of a user specified numeric field in each bucket. | 2014-09-01 |
| Exporting/Reporting | Custom Fieldlist | Creates a variable called v_custom_fieldlist that stores the names of the fields in the input table that match a user specified filter condition and data type. | 2014-09-01 |
| Analysis - Customer Master | Customer Master - Duplicate Address | Reports customer records that have duplicated addresses in a standardized address field and a postal code field based on a 100% match or a numeric match. | 2014-09-01 |
| Analysis - Customer Master | Customer Master - Incomplete Data Entry - Choose Fields | Reports records that contain blanks, 0, or invalid dates and times in a list of up to 5 user defined fields. | 2014-09-01 |
| Analysis - Customer Master | Customer Master - Incomplete Data Entry - Fixed Fields | Reports records that contain blanks, 0, or invalid dates and times in a list of predetermined fields. | 2014-09-01 |
| Analysis - Customer Master | Customer Master - No Terms | Reports customer records that have no payment terms or credit limits listed. | 2014-09-01 |
| Analysis - Customer Master | Customer Master - Overdue Review | Reports customer records that have not had any approved changes in the last N days. Exceptions may prompt a review of credit limits, prohibited persons lists, and so on. | 2014-09-01 |
| Date and Time | Date Loop | Creates a table with a single datetime field containing a record for every day in a user entered date range (inclusive). | 2014-09-01 |
| Date and Time | Date Loop By Record | Creates a record for each day between a start date and end date field on the same record | 2014-09-01 |
| Importing | Define Delimited File by Script | Attempts to define a delimited file. Intended for use only if the IMPORT DELIMITED or ODBC methods are not successful. | 2014-09-01 |
| Project Items | Delete Named Variables Syntax | Creates an ACL table showing DELETE variable_name OK syntax for currently active variables in field Del_Var. | 2014-09-01 |
| Removing | Delete Temporary Tables | Deletes all table layouts and linked FIL files with names starting with T_. | 2014-09-01 |
| Other Utilities | Disable ScriptHub Environment | Restores the user's original Options configuration after completing a ScriptHub script. | 2014-09-01 |
| Project Items | Display Variables | Creates a table listing the variables stored in the project at run time. | 2014-09-01 |
| Other Utilities | Domain User Listing - CSVDE | Exports data from Active Directory Domain Services (AD DS) using CSVDE.exe, to a CSV file and imports the file into an ACL table. | 2014-09-01 |
| Other Utilities | Domain User Listing - NETUSER | Creates a list of user accounts using DOS NET USER and imports select fields into an ACL table | 2014-09-01 |
| Duplicates | Duplicates - Same Keys Different Amount (Monetary Unit Variance) | Identifies records where the combination of one or more key field values is duplicated and the amount in a specified field is within N monetary units. | 2014-09-01 |
| Duplicates | Duplicates - Same Keys Different Amount (Percent Variance) | Identifies records where the combination of one or more key field values is duplicated and the amount in a specified field is within N percent. | 2014-09-01 |
| Duplicates | Duplicates - Same Keys Different Date | Identifies records where the combination of one or more key field values are duplicated within N days of each other. | 2014-09-01 |
| Duplicates | Duplicates - Same Keys Different Field | Identifies records where the combination of one or more key field values are duplicated, with a value in a specified field being different. | 2014-09-01 |
| Date and Time | Elapsed Time - Hours, Minutes, Seconds | Calculates elapsed time between a start and end date/time in hours, minutes, and seconds. | 2014-09-01 |
| Data Quality | Empty Fields | Creates a table with a list of fields that are entirely empty (character, numeric, or date) | 2014-09-01 |
| Other Utilities | Enable ScriptHub Environment | Stores the user's original Options configuration in variables. Sets Options to expected ScriptHub environment. | 2014-09-01 |
| Other Utilities | Error Handling - Quit on Empty | Tests if the open table has 0 records, and if so, QUIT. | 2014-09-01 |
| Other Utilities | Error Handling - Version | Tests the version of the ACL product and exits if the incorrect version is being used. | 2014-09-01 |
| Other Utilities | Error Handling Character Data Type Test | Tests if a specified key field in the currently open table has a character data type. If yes, proceed. If not, exit. | 2014-09-01 |
| Other Utilities | Error Handling Datetime Data Type Test | Tests if a specified key field in the currently open table has a datetime data type. If yes, proceed. If not, exit. | 2014-09-01 |
| Other Utilities | Error Handling Input Table Not Found | Tests if a specified input table exists. If yes, proceed. If not, exit. | 2014-09-01 |
| Other Utilities | Error Handling Numeric Data Type Test | Tests if a specified key field in the currently open table has a numeric data type. If yes, proceed. If not, exit. | 2014-09-01 |
| Other Utilities | Error Handling Numeric or Datetime Data Type Test | Tests if a specified key field in the currently open table has a numeric or datetime data type. If yes, proceed. If not, exit. | 2014-09-01 |
| Importing | Execute FTP Download | Uses EXECUTE with Microsoft's FTP.exe to download a specified file from an FTP site requiring login information. | 2014-09-01 |
| Exporting/Reporting | Execute FTP Upload | Uses EXECUTE to upload a specified file to an FTP site requiring login information. | 2014-09-01 |
| Importing | Execute HTML Download | Uses EXECUTE to download a URL to a file. | 2014-09-01 |
| Other Utilities | Execute Wait | Uses the EXECUTE command in combination with Windows TIMEOUT.exe to pause the command processor for a specified number of seconds. | 2014-09-01 |
| Project Items | Export all Table Layouts to Excel | Exports the layouts of all tables in the current project to Excel. | 2014-09-01 |
| Exporting/Reporting | Export stored tables to Excel | This script opens and exports the tables stored in one or more variables to Excel. | 2014-09-01 |
| Exporting/Reporting | Fill In Header | Fills blanks in a user specified field with the data immediately above it | 2014-09-01 |
| Patterns/Transpositions | Find Patterns in Character Field | Identifies unique patterns in a single character field. | 2014-09-01 |
| Isolating | First and Last Record in a Group | Extracts the first and last record in a group of records sharing the same key fields | 2014-09-01 |
| Date and Time | Fixed Day of the Month | Creates a record for the same day in every full month between a start and an end date, based on the day of the start date. | 2014-09-01 |
| Date and Time | Fixed Day Per Month, by Record | Creates a new record for every fixed day of the month between the start date and end date field on the same record. The fixed day is based on the start date column. | 2014-09-01 |
| Grouping | Flatten Duplicates with List | Flattens duplicates with the same key fields into a single record, with a pipe separated list of different values from a single field (same, same, different concept) | 2014-09-01 |
| Grouping | Flatten Table | Creates a single record out of repeating records with the same key fields by moving all non-key fields from vertical to horizontal alignment. | 2014-09-01 |
| Keyword Searching | Frequently Used Words | Lists all words appearing in a specified field in descending order on count. | 2014-09-01 |
| Project Items | Get Field Metadata | Stores metadata such as the start position, encoding, and format (if any) of a single user specified field in variables for further use. | 2014-09-01 |
| Date and Time | Get Set Date Configuration | Stores the user's original SET DATE configuration in a variable. | 2014-09-01 |
| Exporting/Reporting | Hide View Columns | Creates a view with only those columns that do not contain a user specified value in all records. For example, this script can be used to hide columns that have all blanks or 0 in all records. | 2014-09-01 |
| Importing | Import Delimited Change Field Length | Modifies the length of a single field in an existing IMPORT DELIMITED command and adjusts the start position of all subsequent fields. | 2014-09-01 |
| Importing | Import Delimited Insert New Field | Inserts a single new field into an existing IMPORT DELIMITED command and adjusts the start position of all subsequent fields. | 2014-09-01 |
| Importing | Import Excel All Worksheets | Imports all worksheets and all fields in a specified Excel workbook. | 2014-09-01 |
| Date and Time | Inactivity - Days Old | Identifies all items that have not had any transactions during the previous N days. | 2014-09-01 |
| Data Quality | Incomplete Data Entry | Reports records that contain blanks, 0, or invalid dates in user specified critical data fields | 2014-09-01 |
| Data Quality | Keyfield Profiling | Creates a record count for a set of user specified key fields, and the percentage contribution of each set to the full population. | 2014-09-01 |
| Keyword Searching | Keyword Search - Include | Reports records where a distinct word from a keyword table is found in a specified field. | 2014-09-01 |
| Keyword Searching | Keyword Search Exclude | Reports records where a distinct word from a keyword table is not found in a specified field. | 2014-09-01 |
| Isolating | Last Matching Record | Extracts the last record in a previously sorted table that matches a given filter condition | 2014-09-01 |
| Other Utilities | List Large Files in File System | Lists files in a user specified directory where the file size exceeds a user specified threshold | 2014-09-01 |
| Date and Time | List Sequential Days | Displays records with at least N sequential dates in the same key field group. | 2014-09-01 |
| Isolating | List Sequential Documents | Displays records with at least N sequential document numbers (such as invoice numbers) in the same key field group. | 2014-09-01 |
| Patterns/Transpositions | List to Columns | Concatenates the unique values in the N left-most fields to create new field names. Displays the data from all records in the key field group below the new field name. | 2014-09-01 |
| Project Items | Locate Project Folder | Creates an ACL table with the location of a misplaced folder within the Project Navigator. | 2014-09-01 |
| Project Items | Locate Script | Creates an ACL table with the location of a misplaced script within the Project Navigator. | 2014-09-01 |
| Project Items | Locate Table | Creates an ACL table with the location of a misplaced table within the Project Navigator. | 2014-09-01 |
| Appending/Joining | MCC Code Lookup | Imports and joins a table of user defined known MCC codes to a transactional table | 2014-09-01 |
| Statistics | Minimum and Maximum Values Across All Fields | Creates a table with two records showing the minimum and maximum values of the table's text, numeric, and date fields. | 2014-09-01 |
| Statistics | Minimum and Maximum Values In Record | Creates a table with the minimum, maximum, and average numeric values in a specified number of fields | 2014-09-01 |
| Other Utilities | Notify - Apache ANT | Uses Apache ANT Mail Task with JavaMail to authenticate and send email messages and optional attachments from within Analytics. | 2014-09-01 |
| Reordering | Remove Buckets | Transfers horizontally bucketed fields in a table into new vertical records, grouped by user defined key fields. | 2014-09-01 |
| Removing | Remove Consecutive Spaces | Removes any number of consecutive blank spaces in a specified text field and replaces them with a single blank space. | 2014-09-01 |
| Removing | Remove Duplicate Characters - Consecutive | Removes repeating characters from a string, keeping only the first occurrence in groups of two or more repeating characters. | 2014-09-01 |
| Removing | Remove Duplicate Characters - Non-Consecutive | Removes duplicated characters from a string, keeping only the first occurrence of the character, no matter where the subsequent characters appear. | 2014-09-01 |
| Other Utilities | Repeat Delayed Script | Calls a user provided script N times with a delay of Y seconds | 2014-09-01 |
| Patterns/Transpositions | Replace Selected Characters - All Fields | For every character field in the table, replaces characters with corresponding replacement characters as listed in a user defined character map. | 2014-09-01 |
| Patterns/Transpositions | Replace Selected Characters - Single Field | Within a single specified character field, replaces characters with corresponding replacement characters as listed in a user defined character map. | 2014-09-01 |
| Isolating | Round Amounts | Identifies transactions ending in a user specified round amount, such as 1.00, 25.00, or 100.00, above a user specified threshold. | 2014-09-01 |
| Totals | Running Total | Creates a Running Total field for a single numeric field. | 2014-09-01 |
| Totals | Running Total - Multiple Fields | Creates a running total for one or more numeric fields. | 2014-09-01 |
| Sampling | Sampling percentage of amount | Randomly selects records equivalent to N percent of the total amount in the table. | 2014-09-01 |
| Sampling | Sampling percentage of records | Randomly selects N percent of records from the total population of the table. | 2014-09-01 |
| Exporting/Reporting | Shorten Character Fields (Export to Excel) | Truncates long character fields to 254 characters to facilitate export to Excel. | 2014-09-01 |
| Reordering | Sort Words in Field | Creates a computed field that contains the individual words in a user specified character field sorted in ascending order. This script can be helpful in normalizing data such as employee or vendor names when looking for near matches and duplicates. | 2014-09-01 |
| Data Quality | South Africa ID Number Validation | For South Africa only. Identifies National Identification Numbers where the check digit appears invalid. | 2014-09-01 |
| Isolating | Split Transactions - Fixed Limit- N Days Apart | Identifies possible split transactions to circumvent a fixed limit. Reports transactions with duplicated key fields that occur within N days of each other and in total exceed the specified limit. | 2014-09-01 |
| Isolating | Split Transactions - Variable Limit - N Days Apart | Identifies possible split transactions to circumvent a variable limit. Reports transactions with duplicated key fields that occur within N days of each other and in total exceed the limit for the group. | 2014-09-01 |
| Statistics | Statistics To Table - All Fields | Reports statistical values for all numeric and datetime fields in a table. | 2014-09-01 |
| Statistics | Statistics to Table - Method 1 | Reports statistical values for a single numeric or date field in a table. | 2014-09-01 |
| Sampling | Stratified Random Record Sampling | Selects a specified number of random records from each stratified layer. | 2014-09-01 |
| Repeating Commands | Summarize Multiple Tables | Performs the same SUMMARIZE command in multiple tables with identical structures. | 2014-09-01 |
| Isolating | Superseded Items | Creates a series of new fields to identify a chain or hierarchy of transactions. Also called 'Parent Child Utility' or 'Transaction Chain'. | 2014-09-01 |
| Isolating | Superseded Items (Method 2) | Creates a series of new fields to identify a chain or hierarchy of transactions of superseded items. Also called 'Parent child utility' or 'Transaction Chain'. | 2014-09-01 |
| Date and Time | Time Loop | Creates a record for each time unit between a start and end time. Time units can be hours, minutes or seconds. | 2014-09-01 |
| Totals | Total Numeric Fields in Record (All Fields) | Creates a computed field to total all the numeric fields in a record. | 2014-09-01 |
| Totals | Total Numeric Fields in Record (Selected Fields) | Creates a computed field to total the specified numeric fields in a record. | 2014-09-01 |
| Data Quality | Transaction Table with Summary | Inserts a summary line with counts and numeric totals above a group of records with identical character key field(s). | 2014-09-01 |
| Isolating | Transactions over a fixed limit | Reports transactions where the value in a specified numeric field exceeds a fixed limit | 2014-09-01 |
| Isolating | Transactions Over Variable Limit | Reports transactions that exceed the authorized limit provided for a given key field group. | 2014-09-01 |
| Patterns/Transpositions | Transpose Table | Transposes the rows and columns in a table. | 2014-09-01 |
| Data Quality | UK VAT Number Validation | For UK only. Uses UK VAT number algorithm: Each digit of the VAT Number is evaluated against VAT number creation rules. | 2014-09-01 |
| Isolating | Unauthorized MCC Codes | Imports and joins a table of user defined unauthorized MCC codes to a transactional table | 2014-09-01 |
| Keyword Searching | Unusual Pairings | Using a 'Same Same Different' approach, reports records that contain unusual pairings as per user-defined filters, occurring on the same date. For example, the same employee expenses mileage (filter 1) and gasoline or car rental (filter 2) on the same day. | 2014-09-01 |
| Other Utilities | Unzip File with Password | Uses the 7zip application to extract contents of a user specified and password protected ZIP file to the zipped file's location. | 2014-09-01 |
| Date and Time | Validate Dates | Evaluates all datetime fields in a table and identifies if the date is valid, invalid, or blank. | 2014-09-01 |
| Data Quality | Verify Data To Table | Reports data validity errors across all fields in an ACL table | 2014-09-01 |
| Removing | Wipe First N Records | Deletes the first N records in a table as specified by the user, and keeps the remaining records. | 2014-09-01 |
| Removing | Wipe Table | Keeps first N records in a table as specified by the user, and deletes the remaining records. | 2014-09-01 |