Sample analysis app

A sample analysis app containing one import analytic, one preparation analytic, and one analysis analytic appears below. The analytics perform the following tasks:

 

Note

If you copy any of the syntax below into the ACL Script Editor, make sure to remove all line breaks that should not normally occur in an ACL script – for example, the line breaks in the IMPORT EXCEL command below. Inappropriate line breaks will cause the ACL script to fail.

Sample Import analytic (for use in AX Web Client or the Analysis App window)

 

COMMENT
//ANALYTIC TYPE IMPORT Sample Import analytic
  This analytic imports data from the sample Excel file Trans_May.xls and saves
  it to the new ACL table "Trans_May_raw" (the raw data table).
//PARAM v_input_file F Input File
  Select an input file
//DATA Trans_May_raw
//RESULT LOG
END
 
SET SAFETY OFF
IMPORT EXCEL TO Trans_May_raw Trans_May_raw.fil FROM "%v_input_file%"
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 Import analytic AX (for use in AX Client)

 

COMMENT
//ANALYTIC TYPE IMPORT Sample Import analytic AX
  This analytic imports data from the sample Excel file Trans_May.xls and saves
  it to the new ACL 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

 

COMMENT
//ANALYTIC TYPE PREPARE Sample Preparation analytic
  This analytic prepares the raw data table for analysis and saves it to the
  new ACL table "Trans_May_prepared" (the analysis table). The analytic 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

 

COMMENT
//ANALYTIC TYPE ANALYSIS Sample Analysis analytic
  This analytic classifies the analysis table and outputs the results to the
  new ACL 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 code(s) to include
  Specify one or more merchant category codes to include
//PARAM v_cust_no C OPTIONAL MULTI SEPARATOR , QUALIFIER ' Customer Number(s) 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


(C) 2015 ACL Services Ltd. All Rights Reserved.