Attention Query Writers and Data Analysts:
Can you relate to the struggle of using Query to find every sub-account a membership might have? Or a list of members with only the basic share? The challenge lies with the fact that product records live across 6 different tables – This is a good data practice – But not exactly easy to combine for report queries. It can certainly be done, just not easily.
Which is why I am very very very excited to see that CU*Answers has recently released a helpful new database View with every single account any member has open today.
MBRNACCTSQ – Membership View Active Accounts
You’ll find this gem in your FILExx library (xx = your CU ID) of CU*BASE®. It’s not a comprehensive set of every data point found in the 6 active account tables. Naturally, not every data point applies to every product type, but everything consistent across the records was included plus a few extra.
Views themselves have already been in play behind the scenes for quite awhile here, but until this point only as a solution internally for various programs and APIs. Now, we are beginning to develop and launch Views for data analysts in hopes that these solutions will assist with creating faster and friendlier analytics with Query.
Fancy Tricks with MBRNACCTSQ
Sometimes a tool or resource is developed here with the intent of opening new doors to insights or function that you never had before. Other times a resource is intended to make something that exists faster, better, easier. This is a faster, better, easier solution. Here’s just two ways I recently used this View in a project:
Full Member Name
Normally membership names are split into 3 unique fields – First Name, Middle Initial, and Last Name. That is a good data practice. But combining them all for a final report can get messy with currently available options…like this:
|What You Get||ANDSCAPING, LLC. CALL ME FIRST|
|What You Want||CALL ME FIRST LANDSCAPING, LLC.|
|What You Get||ALICIA M TESTMEMBER|
|What You Want||ALICIA M TESTMEMBER|
There are currently a few round-about ways to achieve a proper-looking combination of the name columns, but none as simple or convenient. Now, only join any table with account numbers to the MBRNACCTSQ View and you instantly have the full membership name for your report, spaced correctly, and in the right order!
Membership “Freshness Date”
Quickly find the last transactional activity across an entire membership account*. Here’s how to do this in Query with this View:
- SORT by Account Base
- BREAK by Account Base
- Set a MAX total on the Last Transaction Date column
- Via the “Create Totals and Sub-Totals” option
- Set output to SUMMARY
- If output is also set to a database file, the resulting file will include a brand-new queryable column of that value for the membership’s most recent transaction date across any suffix.
*This trick does not account for any offline trial balance (OTB) products since OTB products are not included in this View.
I look forward to watching our community of data analysts find new uses for the data resources available at your credit union. Keep an eye on CU*BASE® core release summaries to catch other Views as they go live (Read the Database Changes version)
Or watch the blog, I do plan to spotlight key launches on Views and other analytics or warehousing solutions.