More Pivot Tables

• This is from chapter 34.
  • As well as some other things.
• Start by downloading this file
  • This is information of Sheriff sales in Allegheny County.
  • I found it at Data.gov
  • I added the "about" page, but that came from the same place.
  • I wanted data with a data field and other non-numeric data.
• Copy RawData to WorkingData
• Let's just turn the entire thing into a table.
• Looking it over, the zip code has some problems.
  • I wanted to do an analysis by zip code, so we better fix this.
  • It looks like it was derived from the Address column.
  • In fact, it looks like they just split off the last five characters.
  • This works some of the time, but some of the addresses are in ""
• Some text functions that might help
  • Clean cleans up text by removing all non-printable characters, this won't help.
  • Trim removes extra spaces, but that really won't help either
  • Value will convert text to a number if it can.
  • Left(text,n) returns the left n characters of the text
  • Right(value, n) returns the right n characters of the text.
  • Len(string) returns the length of a string.
• A new zip code line
  • Add a column between Addresses and Municipal.
  • Label this Fixed Zip
  • Try =right(m2, 5)
  • 
  • This is bad in two ways
    • First notice it is left aligned, it is text.
    • Next, notice that the 6th entry has a " at the end.
  • Let's work on stripping the quote, can you do this?
    • An if,
    • char(int) returns the character with that ascii code.
    • The ascii code for a " is 34
    • Or you can do """", which escapes the quote.
    • Wrap the entire thing is a value function
    • 
• Sorting Fixed Zip highest to lowest show some problems.
  • There are a hand full of broken records.
  • We can decide what to do with those, probably fix them by hand and document it
  • There are a bunch that don't have any address entries
  • We could wrap the entire thing in an if(istext(m2), value(),"Unknown")
  • 
  • There are 10 value errors from this.
  • 
  • There are also 6 small zip code values
  • 
  • I think, given time, I would fix these.
    • Looking at the address, I would find the appropriate zip
    • For example, Tarentum has the zip code 15084
    • I would fix this in the one bad record.
  • Just let them for now, but know that we have this problem.
• Can you fix the Plaintiff column by stripping quotes?

  • If it starts with a "
       take the left of the string for length - 1 character
       Then take the right of that for length -2
    	 

  • 
  • 
    • Might as well fix Attorney and Defendant as well.
      • I hid the "bad" text columns.
    • Other fields look good.
      • I bet they were from controlled tables in the database.
    • I need a field I can count.
      • Will ID do?
      • Add a total line to the table.
      • Looks like Docket number will do fine.
    • Build a pivot table.
    • Looking for bad data
      • Drop DocketNumber in the Values column.
      • Drop the fixed Attorney in the Rows
      • Note, we still have some " problems.
        • Let's wait for VBA to fix this problem.
    • Try looking at Fixed Plaintiff
      • Some fields have , others have ;
      • There are two text functions that might work here
        • Replace or substitute
        • Which one do you want and why?
        • 
      • Go back to the pivot table
        • On the Analyze special tab, select Refresh in the Data Workgroup.
        • 
      • Apply a Trim to the entire Fixed Plaintiff computation and refresh
      • Notice, it is getting better, but we still have fixing to do here.
    • You can group data in Pivot tables.
      • There are clearly some duplicates in Allegheny County
      • Click on the Row Labels Filter
      • Filter Contains and add the value Allegheny
      • 
      • Sort these lowest to highest.
      • 
      • Select all the things that should be "Allegheny County" with click and control click.
      • 
      • On the Analyze special tab, in the Group workgroup, select Group Selection
      • 
      • 
      • You can then Change "group 1" to be Allegheny County
      • And you can go to the Design special tab and show subtotals in the Subtotals dropdown in the Layout workgroup.
      • 
      • 
      • Or collapse the group to hide the extra detail.
      • 
      • Notice, this created a new Pivot table filter
      • 
      • We want to look for more Pittsburgh Schools.
        • Filter for "Pittsburgh", but make sure it is Fixed Plaintiff, not Fixed Plaintiff2
        • I grouped all of the City of Pittsburgh into a new group.
        • 
        • Then I combined these groups
        • 
      • Can you do the same with the Pittsburgh Fire Fighters Federal Credit Union?
      • 
      • Notice there is an ungroup as well.
      • There are other fields to group, find one and do it. (BAC, Atlantica LLC, ....
      • Think about the pain involved in cleaning up this data.
    • Save this worksheet where you can find it.