VA. ACL USER’S GROUP

Functions Intermediate to Advanced April 21, 2009 Kimberly M. Taylor, CPA, CISA Chesterfield County, VA

Survey Results

Survey Results

Agenda

 Function Syntax - Brief Overview  Cleaning Up Fields  Conversion Functions  Combining Functions  Numerous If Statements

Function Syntax

ALLTRIM( string ) The word “string” tells you to put the name of an ASCII field in its place Works in expression BETWEEN( value , min , max ) Works in filter or expression The word “min” is your beginning value and must be in the same format as your value field.

The word “value” tells you to put a Field name in its place – it can be a date or a number The word “max” is your ending value and must be in the same format as your value field.

The words in ALL CAPS are your actual functions created by ACL. They are followed by an open and closing parenthesis.

Function Syntax

FIND( string <,field_to_search_in> ) – works in filter or expression Find is your function.

In this instance, “string” is the text you want to find is an optional paramater, because of the brackets.

FIND( “yellow” ) – in this instance it looks in all fields.

I have personally found ACL does not consistently work well Without a field name here.

FIND( “yellow” , carcolor1)

Cleaning Up Fields

 String functions (text)  Conversion Functions

SPLIT( string , separator , segment <,text_qualifier> )

 Works in expression to create a new expression, does not work in filter  Splits a field based on a particular separator   Segment – which segment do you want? 1, 2, 3, etc.

SPLIT (name, “, “, 1)

UPPER( string ) or LOWER( string )

 Changes text to all caps or all lower case  Text must be identical for relate tables or join commands to work

INCLUDE( string , characters_to_include )

Field called ID_NO – looks like Taylor040909. The ID number is generated by computer as last name and date.

INCLUDE (ID_NO, “abcdefghijklmnopqrstuvwxyz”) Result = Taylor

EXCLUDE( string , characters_to_include )

Field called ID_NO – looks like Taylor040909. The ID number is generated by computer as last name and date.

EXCLUDE (ID_NO, “abcdefghijklmnopqrstuvwxyz”) Result = 040909

CONVERSION FUNCTIONS

 STRING( number , length <,format> )  Converts a number to text  Works well when trying to use classify command or some other command that needs text

VALUE( string , decimals )

 Converts text fields to numeric fields  Good for use in trying to sort data or using functions that require a numeric field (i.e. summarize

Audit Step Examples

 Numerous If Statements  Combining Functions – When combining functions in same expression, ACL works from the middle expression to the outer expression  Calculations

Audit Step: Verify if mileage was billed correctly  Challenges: – Upgrade during the year for purchased software caused mileage data to be in up to 3 different fields – More than one transport record for same call – could be duplicate records, could be more than one person transported, could be one unit assisting and another unit transporting – Internal file needs to be compared to external billing file – different field formats for ID number – Combining data from 3 different internal tables to come up with one mileage record – Internal data set had # of miles, external data set had $ amount billed – Had to round mileage up to next whole mile for accurate billing calculations

Analysis

 Determine transport mileage on internal data  Determine miles billed on external data  Compare 2 files to determine accuracy of bills

Data

 Internal records = 74,564 records – Contains non-billable records  External records = 19,691 records – Contains mostly billed records only

Determining transport mileage

    Computed Fields Multiple If Statements Combining Functions Converting Text to Number for calculation purposes Edit, table layout

Determine Transport Mileage Step 1: Field Name and Default Value      Default value – field type must match remaining values established “text” or “9999.99” = text field 9999.99 = numeric field # of decimal places equals decimal places in default value. If you put 9999 in default value, you will have 0 decimal places in all fields.

If you are doing multiple if statements, put something unusual in default value so you can check to be sure your remaining if statements are calculating properly.

Determine Transport Mileage Step 2: first If statement  Lodmileage is a numeric field. If it is greater than 0, then use that field as the transport mileage.

Determine Transport Mileage Step 3: second if statement Xportsec = ascii field, field that recorded mileage in 1 st version of software   Combined expression – ACL works from inside expression to outside expression First, – INCLUDE - acl is including numbers and .

– VALUE – acl is converting it to a number with 2 decimal places   Second,, – Do what is in parenthesis first – then determine if the field is greater than 0 If all is true, then it inserts the value

Determine Transport Mileage Step 4 – third if statement  If all three fields that contain mileage are equal to 0  Input 0.00 into transport mileage

Determine Transport Mileage Step 5: fourth if statement  Assumption – lodmileage is the correct field  Use dstmileage if lodmileage is 0 and dst mileage greater than 0  If not, we wanted to use dstmileage

Determine Transport Mileage Step 6: fifth if statement    We had 1 record that did not fit into the rest of the categories, it was coming up as 9999.99

So we just added an if statement to put the actual value in based on data we could see This represents the value of using a default value that ensures your formulas are working

Determine Transport Mileage Result: new field with mileage value

Calculate Miles Billed – external data source

 Add a new expression field  Existing data has a separate field for mileage dollars charged

Calculate Miles Billed Step 1: Field name and default value

Calculate Miles Billed Step 2: First if Statement Miles billed prior to 4/1/08  Different billing rates before and after 4/1/08.

 Pulled in only internal calls

Calculate Miles Billed Step 3: Second If Statement – Miles billed after 4/1/08  Accounted for billing rate after 4/1/08

Calculate Miles Billed Step 4: Third If Statement – Records not associated with this provider  Other volunteer unit information is also in database. Wanted to weed those out as accurately billed, but not a billable record.

Calculate Miles Billed Results – new field with miles billed

Analyze Results – join internal and external data source

  Had 2 create fields in both tables to link results External Data Source  Internal Data Source  Run join command and include all primary and secondary to create a new table

Analyze Results – was billing accurate?

Analyze Results – was billing accurate?

 Yes: (prov_name = "CHESTERFIELD FIRE DEPARTMENT") AND BETWEEN( (Recalculate_Mileage Transport_Mileage) , 0, 1)  NC = Not Chesterfield prov_name <> "CHESTERFIELD FIRE DEPARTMENT"

Analyze Results – was billing accurate?

   NO: (prov_name = "CHESTERFIELD FIRE DEPARTMENT") AND BETWEEN( (Recalculate_Mileage - Transport_Mileage) , -1, 0) NO: (prov_name = "CHESTERFIELD FIRE DEPARTMENT") AND ((Recalculate_Mileage Transport_Mileage) > 1) NO: (prov_name = "CHESTERFIELD FIRE DEPARTMENT") AND ((Recalculate_Mileage Transport_Mileage) < -1)

Analyze Results: Was billing Accurate

 Classify Command  Next steps: sample the no records, recommend they be reviewed by Fire if sample turns out there was incorrect billing

Results

 $7,667 not billed due to not rounding mileage up to next whole mile no yes

Accurate Billing Totals Count

3,427 10,416 13,843

Percent of Count

24.76% 75.24% 100%

Audit Step – review audit trails to determine if ID number was changed on a medical record that has a bill  Concerns – ID numbers for records can be changed – Once ID number is changed, there is no record on the front end that the transport ever occurred – there is a bill with no medical record

Audit Step – review audit trails to determine if ID number was changed on a medical record that has a bill  Challenges – Over 3,000,000 audit trail records on a very short data set – Department is unable to run reports on their audit trail data – New text field and old text field is a combination of all fields changed in the record – wanted to focus on ID number only – Figure out if ID number was actually changed – Link to billing data from external file to determine if there is a bill on the old record

Audit Step – analyze ID numbers

 Extracted all audit log records associated with table that contains ID numbers – Reduced from 3,000,000 records to 68,000 records  Isolated new and old ID numbers using split function  Added new expressions to determine if new ID number was different than OLD ID number  Joined records to billing data to determine if bills went out on OLD ID number

Analyze ID numbers

 Split old and new fields  Create field that identifies old field that had an official transport number starting with FR0  Create field that identifies records where old and new number do not match

Analyze ID Numbers - Results

 38 records where old ID number in medical record matched a bill in the external billing data

Summary

 Learned where to apply functions in filters, expression fields and ACL commands (i.e. – classify)  Function Syntax  Computed Fields  Combining Functions  Multiple If Statements  Cleaning Up Fields  Conversion Functions