310 likes | 323 Views
To Ping or Not to Ping. An exercise working with a large volume of electronic data. Viewing Data. Can be intimidating Can be confusing Can be limited Can be time consuming Can be frustrating Can simply be SCARY. Intimidation. Intimidating.
E N D
To Ping or Not to Ping An exercise working with a large volume of electronic data.
Viewing Data • Can be intimidating • Can be confusing • Can be limited • Can be time consuming • Can be frustrating • Can simply be SCARY
Intimidating • The next couple of slides show large volumes of data that were submitted for an audit recently completed. • The first slide shows three Zip Files. Each file contains data for January, February and March. • The second slide shows the January file opened. • The third slide is the January 2nd text file opened.
Intimidating cont. • Zip file for Jan. Feb. and March.
Intimidating cont. • Data for each day of the month
Intimidating cont. • Data from January 2nd. There are 319,876 lines of data.
Confusing • What to do? • Through discussions , along with other items you should determine: • Column Headings; • What the data means; • Is the information used for reporting; and • If available, the raw data.
Confusing cont. • Looking back at the previous slide: • There are no column headings; • There is no consistency in column length; • Portions just look messy and hard to read or understand.
Limitations • The files received are text files. • Working with Distance software programs may require conversions into other formats. • Such files include: • Excel Files; • Access Files; • CSV Files.
Re-Formatting • When working with different formats, you will need to know any limitations you may have. • We found that when working in Excel we were limited to the number of lines of data that could be saved. • The next slide shows data in a text file from January 1st. There are 151,483 lines of data.
Reformatting cont. • The Excel file only saved 65,536 lines of data. As a result we used an access file.
Time Consuming/Frustrating • Since we were not able to use Excel, the files were re-formatted using access files. • As part of the re-formatting, each of the monthly files were combined into one file. By adding this step, vehicle data for each month was consolidated for audit purposes. • This step was time consuming and frustrating due to lack of experience working with access.
Access File • There are 10,634,152 lines of data in this file.
Time Consuming/Frustrating • With experience • time decreased; • Frustration due to inexperience was also reduced, if not eliminated. • Although a considerable amount of time was spent re-formatting the files, time needed to route the sample vehicles was greatly reduced.
Back to limitations • After completion of the audit, system updates decreased excel limitations. • The next couple of screen shots shows the January 2nd data. • In both cases you will see that all 319,876 lines of data have been captured from the original text file.
Limitations • Sorted by Date and time.
Limitations • Sorted by unit.
What’s Next? • Plot the Pings? • How? • One point/location at a time? Or • load large volumes at once?
What’s Next? • Answer- LOAD DATA AT ONE TIME!!! • Many of the providers of distance programs provide for uploading batch data. You may need to contact each to find out what capabilities may be available. • You may need to obtain additional products or resources. • There may be limitations.
Plotting the Pings • Starting with the access file: • Open the file; • Find the unit number. Unit 1274 was selected; • Filter the selection; • At this point only the data for unit 1274 remains: • You may need to sort by some other criteria. In this case, we sorted by the date/time to sequence the event properly.
Plotting the Pings • Open the distance program and paste the list.
Plotting the Pings • Run the Pings.
Plotting the Pings • Generate reports.
Summary • Working with large volumes of Data can be scary, intimidating and at times frustrating. • Using the tools and resources available can lesson the intimidation and shorten the time needed to complete your audit engagement. • Communication, experience and understanding your limitations can reduce frustration.
Questions??? • Questions • Comments • If time permits, live demonstration.