A script is a series of Analytics commands that are executed sequentially and used to automate work within Analytics. Any Analytics command can be contained in a script.
Why should I use a script?
There are a variety of benefits to using a script.
Automate processes
Do you need to perform a series of repetitive tasks or routines on a regular basis? Are you currently performing these tasks manually? If so, you can probably use a script to automate these types of processes. By using a script, you can avoid manual efforts associated with complex routines. The more complex the routine, the more time will be saved by running a script.
Schedule processes
Scheduling scripts is often essential when you are dealing with large data sets. If you are using Analytics Exchange, you can run scripts on a schedule, even outside of work hours. You can also schedule a single script or series of scripts to run at a specific date and time.
Improve accuracy
When performed manually, complex data analysis routines are prone to human error. By using a script, you can ensure process consistency and precision. You can also be absolutely certain that the same instructions will be executed in the same order each time the same script is run.
Reduce complexity
Scripts are able to process complex file structures and make complex computations on data fields. Sometimes, more complex analysis can only be performed with a script. For example, continuous monitoring programs often require scripts to automate processes.
Share analysis
Scripts are portable and sharable. They can be sent to other users, made available in network locations, and copied between Analytics projects.
Allow user interaction
Scripts can be designed to prompt users for input, allowing users to run them against their own uniquely named tables and fields, using their own input criteria.
Capture documentation
Scripts are a great source of documentation for audit reviews, and can be used as part of an audit trail. By creating a script, you are documenting the process of creating the results of an analytic test - which is something that can be easily referenced in the future. You can also add comments to scripts to further supplement the documentation.
Common processes that can be automated by scripts
A script may be something as simple as running a command on a single field, or it may be substantial enough to perform the bulk of the work to achieve your analysis objectives.
Scripts are most commonly used to perform one or more of the following processes:
Import data
You can use a script to import various source files into Analytics, including fixed-width, delimited, report/PDF, Excel, and files accessed via ODBC.
COMMENT *** Imports data from a Microsoft Access database file to an Analytics table named employees_list.
IMPORT ACCESS TO employees_list PASSWORD 1 "C:\ACL DATA\Sample Data Files\employees_list.fil" FROM "Employees_List.mdb" TABLE "[Employees_List]" CHARMAX 60 MEMOMAX 70
Prepare data
You can use a script to prepare data for analysis. Scripts can be used to standardize fields prior to joining or relating tables, remove leading or trailing spaces from values, remove unwanted characters, and convert data types of fields.
COMMENT *** Creates a new computed field containing the PO_No value. All leading blank spaces are removed so that the value is properly left justified.
DEFINE FIELD c_PO_No COMPUTED ALLTRIM(PO_No)
Analyze data
Scripts use data analysis commands and functions to achieve analysis objectives. You can use a script to group records, make comparisons, and identify issues, trends, or outliers.
COMMENT *** Opens Sales2016Actual table, classifies on Customer Number, subtotals on Sales Order Amount, and sends the results to Sales2016ByCustomer.
OPEN Sales2016Actual
CLASSIFY ON Customer_Number SUBTOTAL Sales_Order_Amount TO Sales2016ByCustomer
Example script
Scenario
Each month, a client provides you with vendor, invoice, and purchase order information. You need to verify the integrity of the data by ensuring that there are no blanks in the purchase order field.
You decide this is a good opportunity to write a script, given the repetitive nature of the task. You want to have all fields available for analysis and be able to search the purchase order field for blanks.
Process
You create a script that performs the following actions:
- Opens the Invoice_Amts table.
- Searches the purchase order field (PO_No) for blanks.
- Extracts records with blank purchase order numbers to a new table (r_Blank_Purchase_Orders), allowing you to follow up with exceptions.
Tip
To easily identify tables, you can use the following naming conventions:
- Prepared table prefix table name with p_
- Temporary table prefix table name with t_
- Results table prefix table name with r_
Result
COMMENT *** Opens table "Invoice_Amts".
OPEN Invoice_Amts
COMMENT *** Searches for blanks in the purchase order field.
SET FILTER TO ISBLANK(PO_No)
COMMENT *** Extracts results to a new table called "r_Blank_Purchase_Orders".
EXTRACT FIELDS Vendor_Name Invoice_No Payment_Date Invoice_Date Invoice_Amt Vendor_Name Invoice_No PO_No TO r_Blank_Purchase_Orders
Next steps
Complete the short tutorial "Your first Analytics script" and try creating your own script.
