The Lightbulb Went Off…Add to Member & Y/N Conversions

I’ve recently been working on a hefty project that is stretching my skills with custom queries up to a new level, primarily with its requirements for very specific formatting and column rules. The part that became my lightbulb moment relates to hand-crafting Y/N (Yes/No) indicators on various pieces of information that do not already live in CU*BASE(r) data as Y/N.

Quite a few were cases where CU*BASE is storing data as 0/1 instead. A few of the cases were based on conditional logic where if the answer is ____, then Y, if anything else, then N. It would be easy if I could find all the ‘Y’ and all the ‘N’ could simply be left blank. And some vendors or file formats can be ok with that. But in my case I did need to have both Y and N designated.

The lightbulb moment was when I started plugging through this process and to be frank, I was a tiny bit annoyed by the fact that I even needed to do it….then I thought to myself – “Hey! As annoying as it is to man-handle this data into a Y/N list for a vendor, I bet there’s a lot of people who don’t know they can do this themselves.”

You probably don’t all have the time to DIY this, but I hope you’ll at least take away the understanding that it can be done. The short explanation of what I’m covering today is a 3-part process:

  1. Collect all the ‘Y’
  2. Collect all the ‘N’
  3. Stack the 2 data sets together so that the Y/N now live together, in the same column.

The long explanation is going to get a little technical so fair warning, you may not find it interesting. Feel free to skip if you’re not a query-writer. 🙂 This blog post is not intended to be a full step-by-step. I did start writing it out as the blow by blow account and it was getting too long. If you’re interested in pursuing all this on your own and have specific questions, just reach out to me.

Crafting a Custom Y/N Indicator

There’s 2 tactics that need to be used in combination, both of which are pretty basic-looking on the surface, but if you’ve never used them before you’ll wonder how they actually work. So, without further ado:

  1. Calculated/Custom Fields – Y or N value
  2. Output option of Add to Member

The Y and N

The Y/N conversion strategy involves using this feature to populate a static character into every row of a table. With the “Create Custom Fields”, follow these steps.

  1. Set a FIELD NAME (anything you want)
  2. Enter a ‘Y’ value under the EXPRESSION section. that’s literally it – type in the single quote, capital Y, and another single quote.

The Y value is now coded onto every single row in the table. This is good, but not good at the same time – not everybody actually qualifies as a Y = yes, right? Don’t forget to also set up appropriate filters in the “Choose Records” section of your query definition.

Also, don’t forget to Use the “Choose Fields” section to confirm your brand-new handmade field is available within your data.

Add to Member

The full strategy involves breaking apart the Y and N collections as a string query and using Add to Member output option to combine the separate results of Y vs N. Depending on the field you’re working with, you might be able to get it done in 2 steps:

STRING1 = collect the Y results, create the Y indicator, and use “replace file” output option to create a new data table called YNLIST1. It will only hold the Y results at this point.

STRING2 = collect the N results, create the N indicator, and use “add to member” output option to stack these new results into the same YNLIST1 data table you just created in STRING1. Now the YNLIST1 table holds both the Y and the N results. Read more about output options.

That’s it! Now in a STRING3 step, or any other step down the string, you can now join in the YNLIST1 table with its new Y/N column per member (or per account).

More Custom/Calculated Fields

I’ll leave you with a few small additional notes about custom/calculated field options. These are often my best friends when I build queries. I use several options pretty heavily to help:

  • Manage date formats (although now, that DATELOOKUP table I recently wrote about is pretty handy)
  • Do math with data (DBLMT – CURBAL = Available Balance)
  • Pick out sub-sections of a field that holds more than I need (ZIPCODE is 9-digits and I pull out only the first 5 digits to download for my mailing list)

All these and a few other popular options are all documented in CU*BASE Help – Read it here and have fun!

Share your thoughts

14 Replies to “The Lightbulb Went Off…Add to Member & Y/N Conversions”

  1. Very insightful as usual Annalyn. I will have to try this when I have time to dive into creating some custom data. Thanks for the tips!

    1. Always happy to share! Thank you for the compliment – Reach out if you try this and get stuck at any point. 🙂

  2. This was interesting to think about ways I would be able to incorporate this into some of my queries! Custom Fields has changed a lot of what I am able to do within Query, so learning about more options is always exciting.

    1. Becoming more familiar with Custom Fields is also the point where I began to see the potential for the more creative ways to do data mining. If you have any cool tricks, reach out and I could include it in a document we’re developing to cover more of the Custom Field options. 🙂

  3. Pretty cool! It’s easy to forget that Query was designed to be extremely powerful, albeit not always immediately obvious. It’s possible to do some pretty complicated things with it.

    1. Yes! So much more powerful than is realized today. Although, I mentioned to Shanna and I’ll say again here because it bears repeating….I probably didn’t stress strongly enough in the post that stuff like this is COOL, but should only be used after considering…is it NECESSARY? Have to think about whether it’s worth the time to re-format vs just pull the report & educate on how to read it (or provide a legend).

  4. Nice. I always appreciate the breakdown in plain English, and where I can find the technical explanation in a help document. I am often called on to pull data on the fly, and formatting it the way the user wants to see it can get complicated. I’m no expert, but being able to add these details to a report is something helpful to add to my toolset.

    1. Exactly – Another resource/strategy added to your toolset. You don’t HAVE to use it, but you know you can if you ever need to. I also love the point that even though it’s a little extra work on the Query side, there’s a couple benefits to devoting the time – 1) A smooth, intuitive presentation to your audience. They don’t even know they would rather see it as Y/N vs 0/1, but you can anticipate and meet that need before-hand. – 2) Depending on the audience, a small time savings when you don’t need to spend time teaching them what the raw data means. Less distraction from the point of what you’re trying to communicate with the data. Still, this is the kind of thing that is cool, but should only be used if there’s a purpose for it. Much faster to build Query reports without this tactic. 🙂

  5. This information will be very helpful as I continue down the path of recreating specific reports. Thank you for the great tips! There are many scenarios that I would find this helpful, such as indicating if a member has a certain product or even the email address field. While, I understand what 0/1 means, it will be easier to create a custom field and everyone will be able to understand.

    1. Glad to hear this will be helpful to you! I will say though – I probably didn’t stress strongly enough in the post that stuff like this is COOL, but should only be used after considering…is it NECESSARY? Have to think about whether it’s worth the time to re-format vs just pull the report and educate on how to read it or provide a legend. I’m conflicted on this. Normally I wouldn’t go to the extra trouble of working it out in my queries, but depending on the audience, if it’s a recurring report vs one-time, etc etc – different story maybe. In any case, it’s a very cool trick and as Dawn said earlier…nice to be reminded that Query can do more than is realized sometimes.

      1. You know I love learning all of these secret query tricks! I am excited to start building my many custom queries and utilizing all of the new tricks that I recently learned! But I do agree with your statement, that some techniques are more useful and worth the time if it’s for a recurring report vs. a one time report. I will keep that in mind!

  6. Great write up Annalyn! One thing I’ve lived through and definitely learned is that training query can be HARD if someone doesn’t have experience with it in some fashion or another. Harder still is when you break beyond the basics and start doing some of the more advanced things – there’s not a ton of good information out there that isn’t buried in long drawn out technical documents, and even then you’re digging for a needle in a haystack to find what is relevant to you in your specific use case.

    When I originally started at Honor, my mentor knew enough to just get by, and that was it. For me to push harder or further, the path of least resistance was to dump data into excel and do all the cool things in Excel. If you continue doing write-ups like this, or even if we start crowd-sourcing the development of articles like this, we could come up with a really great CU*Base-Specific Advanced User Guide that could serve as a tool for new users and veterans alike to learn new tips, tricks, and tools in query that could really build the confidence of users throughout the network. This is exciting stuff!

  7. As someone who is still learning the ins and outs of query, it is easy to forget just how powerful it is can be when you combine it with custom fields. As Zak mentioned, it would be really informative to see of all the different ways people use the query tools in a condensed form. I enjoy reading all of the query blog posts you have been posting and I am looking forward to the next one!

  8. Using and building queries are a weak point for myself, it is nice to get an overview on how to use the custom queries for something that would have taken a lot longer to find by just digging it. I am going to have to start using them a lot more moving forward! It would be very interesting to see some examples how other credit unions use custom queries.

Leave a Reply

Your email address will not be published. Required fields are marked *