1

Current scenario

  • I'm a data tester, and I test insurance contracts, and each policy has many "roles", "features", "history items" etc.
  • A standard SQL query with all data joined will return 20-30 rows all jumbled together
  • We need a way to review each policy (aka contract) in an organized and systematic way
  • So, instead of creating 1 big query, we create 7 related queries. Each related query uses "contract #" as a PK (primary key). We put all the queries in a workbook and we're well on our way.

Issue

  • The problem is, we still have a lot of data processing to do, we can review each "policy" across all these sheets
  • We need a simple, efficient and highly automated method to review our Excel workbook
1
  • I'm a tester. I'm only a tester. Everything I do is as a data quality assurance and/or software quality assurance tester. The work in this post was 100% related to testing. I have no idea why a few of you deemed it "...unrelated to testing" – Paul Ehrsam Jun 2 '18 at 4:26
1

solution

  • create a "summary" worksheet that connects all related worksheets
  • this concept works for any data with a shared PK (primary key)
  • all we're really doing is using standard VLookup functionality of Excel, but with an important twist
  • instead of hard coding our VLookups we'll use Excel's INDIRECT function to execute our VLookups based on column headings
  • the big advantage here is that we can add/remove/change columns and since our ODB (operational database) has 1,200 tables, we have a lot of variations (beyond the scope of this overly simplistic Q&A)

enter image description here

enter image description here

Not the answer you're looking for? Browse other questions tagged or ask your own question.