Transcript VA. ACL USER’S GROUP - Virginia ACL Users Group
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
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