SPX 1

Basic Efficiency Tool to Automate Your Data Extraction & Transformation

Read_Form_Generate_SPX

Read_Form_Generate_SPX

This command can be used to test transformation of an Excel input form whether it can be exported a transaction list which follows the standard import format. In addition, it is optional to output the SPX transformation script.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the standard import format of transaction file.

Command with Parameters
Read_Form_Generate_SPX “Y” “C:\LB\Form.xlsx” “Entry” “C:\LB\Form.xlsx” “Transaction”

Is Output SPX

Specify whether to output the SPX transformation script
e.g. Y or N

Source File Path

Specify full file path of an Excel Workbook which contains an entry form
e.g. C:\LB\Form.xlsx

Source Worksheet

Specify a worksheet name of the file
e.g. Entry

Target File Path

Specify target file path of the testing results of transformation
e.g. C:\LB\Form.xlsx

Source File Path: C:\LB\Form.xlsx    Source Worksheet: Entry

Is Output SPX: Y

Worksheet = Entry
Common Value = Analysis Version, Currency, Analysis Department, Account, Ledger, Analysis Office
Cross-Tab
{
Row = Analysis Product, Analysis Segment, Analysis Market
Column = Date
Value = Amount, Unit Price, Quantity
Value Header Exist = Y
}
Output Setting
{
Insert File = C:\LB\Form.xlsx , Transaction
}

Target File Path: C:\LB\Form.xlsx    Target Worksheet: Transaction

Run_SPX_Combine_Worksheet

Run_SPX_Combine_Worksheet

This command can be used to copy multiple transaction lists and merge these to a single transaction list in accordance with setting of a SPX transformation script. Validation list can be generated to notify errors.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to use the command “Read_Form_Generate_SPX“.

Command with Parameters
Run_SPX_Combine_Worksheet “C:\LB\SPX.lts” “C:\LB\Form.xlsx” “Combine” “Validation List”

SPX Script

Specify full file path of the SPX transformation script
e.g. C:\LB\SPX.lts

Source File Path

Specify full file path of an Excel Workbook which contains multiple transaction lists
e.g. C:\LB\Form.xlsx

Combine Worksheet

Specify a worksheet name to save the combined transaction list
e.g. Combine

Validation Worksheet

Specify a worksheet name to save the validation list
e.g. Validation List

Source File Path: C:\LB\Form.xlsx

SPX Script: C:\LB\SPX.lts (Specify Columns for Combining Multiple Worksheets)

Worksheet = Transaction1,Transaction2
Column = Analysis Office, Ledger, Account, Analysis Department, Currency, Analysis Version, Quantity, Unit Price, Amount, Analysis Market, Analysis Segment, Analysis Product, Date

SPX Script Optional Setting

Mandatory Column = Analysis Office, Ledger, Account, Analysis Department, Currency, Analysis Version, Quantity, Unit Price, Amount, Analysis Market, Analysis Segment, Analysis Product, Date

Combine Worksheet: Combine     Validation Worksheet: Validation List

Set_Var_SPX_Combine_Worksheet

Set_Var_SPX_Combine_Worksheet

This command can be used to copy multiple transaction lists and merge these to a single transaction list in accordance with setting of a SPX transformation script. Validation list can be generated to notify errors. In addition, an assigned variable will be returned a value “N” to notify at least one error is detected.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to use the command “Run_SPX_Combine_Worksheet” and how to create and operate LedgerBase variable.

Command with Parameters
Set_Var_SPX_Combine_Worksheet “Is No Error Detected” “C:\LB\SPX.lts” “C:\LB\Form.xlsx” “Combine” “Validation List”

Variable Name

Specify a variable name to store a value whether nothing error is detected
e.g. Is No Error Detected

SPX Script

Specify full file path of the SPX transformation script
e.g. C:\LB\SPX.lts

Source File Path

Specify full file path of an Excel Workbook which contains multiple transaction lists
e.g. C:\LB\Form.xlsx

Combine Worksheet

Specify a worksheet name to save the combined transaction list
e.g. Combine

Validation Worksheet

Specify a worksheet name to save the validation list
e.g. Validation List

Set_Var_SPX_Combine_Worksheet

Example by Process Flow Diagram

Process Flow Diagram

Source File Path: C:\LB\Form.xlsx

SPX Script: C:\LB\SPX.lts (Specify Columns for Combining Multiple Worksheets)

Worksheet = Transaction1,Transaction2
Column = Analysis Office, Ledger, Account, Analysis Department, Currency, Analysis Version, Quantity, Unit Price, Amount, Analysis Market, Analysis Segment, Analysis Product, Date

SPX Script Optional Setting

Mandatory Column = Analysis Office, Ledger, Account, Analysis Department, Currency, Analysis Version, Quantity, Unit Price, Amount, Analysis Market, Analysis Segment, Analysis Product, Date

Combine Worksheet: Combine     Validation Worksheet: Validation List

Custom Workspace: The created variable “Is No Error Detected” was returned a value “N” to notify at least one error is detected.