Sample analytic scripts

The sample analytic scripts contain one import script, one preparation script, and one analysis script. The analytic scripts can be run in Robots.

Sequence of the analytic scripts

The three analytic scripts are designed to work in conjunction, and need to be run in the following sequence:

Sequence	ANALYTIC TYPE	Analytic script name
1	IMPORT	Sample Import analytic Robots_AX
2	PREPARE	Sample Preparation analytic
3	ANALYSIS	Sample Analysis analytic

Sample import analytic script

Imports data from the sample Excel file Trans_May.xls and saves it to the new Analytics table Trans_May_raw (the raw data table).

Analytic script name	Use in	Import file requirement
Sample Import analytic Robots_AX	Robots	RobotsTrans_May.xls must be located in the Input/Output tab in the same robot as the analytic script

Sample import analytic script for use in Robots

COMMENT
//ANALYTIC TYPE IMPORT Sample Import analytic Robots_AX
  This analytic script imports data from the sample Excel file Trans_May.xls and saves it to the new Analytics table "Trans_May_raw" (the raw data table).
//FILE Trans_May.xls
//DATA Trans_May_raw
//RESULT LOG
END

SET SAFETY OFF
IMPORT EXCEL TO Trans_May_raw Trans_May_raw.fil FROM "Trans_May.xls" TABLE "Trans2_May$" KEEPTITLE FIELD "CARDNUM" C WID 22 AS "" FIELD "CODES" C WID 4 AS "" FIELD "DATE" D WID 10 PIC "YYYY-MM-DD" AS "" FIELD "CUSTNO" C WID 6 AS "" FIELD "DESCRIPTION" C WID 95 AS "" FIELD "AMOUNT" N WID 9 DEC 2 AS ""
SET SAFETY ON

Sample preparation analytic script

Prepares the raw data table for analysis and saves it to the new Analytics table Trans_May_prepared (the analysis table). The analytic script defines a shorter version of the "Description" field because classifying only supports field lengths up to 64 characters.

COMMENT
//ANALYTIC TYPE PREPARE Sample Preparation analytic
  This analytic script prepares the raw data table for analysis and saves it to the new Analytics table "Trans_May_prepared" (the analysis table). The analytic script defines a shorter version of the "Description" field because classifying only supports field lengths up to 64 characters.
//TABLE v_RawTable Table to prepare
  Select the raw data table you want to prepare
//RESULT TABLE Trans_*_prepared
//DATA Trans_*_prepared
//RESULT LOG
END

SET SAFETY OFF
OPEN %v_RawTable%
DEFINE FIELD DESC_SHORT     ASCII      43  64
EXTRACT RECORD TO "Trans_May_prepared"
SET SAFETY ON

Sample analysis analytic script

Classifies the analysis table and outputs the results to the new Analytics table Classified_Trans_May_prepared (the results table). Users can specify which field to use for classifying the table, and can specify merchant category codes, customer numbers, and date and transaction amount ranges to restrict which records are processed.

COMMENT
//ANALYTIC TYPE ANALYSIS Sample Analysis analytic
  This analytic script classifies the analysis table and outputs the results to the new Analytics table "Classified_Trans_May_prepared" (the results table). You can specify merchant category codes, customer numbers, and date and transaction amount ranges, to restrict which records are processed.
//TABLE v_AnalysisTable Table to classify
  Select the analysis table you want to classify
  //FIELD v_FieldA C Field to classify on
  Select the field you want to classify on
//PARAM v_codes C MULTI SEPARATOR , QUALIFIER ' VALUES |4112 Passenger Railways|4121 Taxis/Limousines|4131 Bus travel|4215 Courier Services - Air or Ground|4411 Cruise Lines|4457 Boat Leases and Boat Rentals|4722 Travel Agencies and Tour Operations|4814 Local/long-distance calls|5812 Restaurants|5813 Drinking Places (Alcoholic Beverages)|5814 Fast food restaurants|5921 Package Stores, Beer, Wine, Liquor|5993 Cigar Stores & Stands|5994 Newsstand|7216 Dry cleaners| MC codes to include
  Specify one or more merchant category codes to include
//PARAM v_cust_no C OPTIONAL MULTI SEPARATOR , QUALIFIER ' Customer Numbers to exclude (optional)
  Specify one or more customer numbers to exclude. Press "Enter" after each number, so that each number is on a separate line. Do not enclose numbers in quotation marks.
//PARAM v_start_date D VALUES |05/01/2003|05/02/2003|05/03/2003|05/04/2003|05/05/2003|05/06/2003|05/07/2003|05/08/2003|05/09/2003|05/10/2003|05/11/2003|05/12/2003|05/13/2003|05/14/2003|05/15/2003|05/16/2003|05/17/2003|05/18/2003|05/19/2003|05/20/2003|05/21/2003|05/22/2003|05/23/2003|05/24/2003|05/25/2003|05/26/2003|05/27/2003|05/28/2003|05/29/2003|05/30/2003|05/31/2003|Start Date
  Select a start date
//PARAM v_end_date D End Date
  Enter an end date or pick one from the calendar
//PARAM v_min_amount N Minimum Amount
  Enter a minimum amount
//PARAM v_max_amount N Maximum Amount
  Enter a maximum amount
//RESULT TABLE Classified_*
//RESULT LOG
END

SET SAFETY OFF
OPEN %v_AnalysisTable%
IF NOT ISDEFINED("v_cust_no") v_cust_no = ""
GROUP IF v_cust_no = ""
  CLASSIFY ON %v_FieldA% IF MATCH(CODES, %v_codes%) AND BETWEEN(DATE, v_start_date, v_end_date) AND BETWEEN(AMOUNT, v_min_amount, v_max_amount) SUBTOTAL AMOUNT TO "Classified_%v_AnalysisTable%.FIL" OPEN
ELSE
  CLASSIFY ON %v_FieldA% IF MATCH(CODES, %v_codes%) AND NOT MATCH(CUSTNO, %v_cust_no%) AND BETWEEN(DATE, v_start_date, v_end_date) AND BETWEEN(AMOUNT, v_min_amount, v_max_amount) SUBTOTAL AMOUNT TO "Classified_%v_AnalysisTable%.FIL" OPEN
END
SET SAFETY ON