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:
- Collect all the ‘Y’
- Collect all the ‘N’
- 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:
- Calculated/Custom Fields – Y or N value
- 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.
- Set a FIELD NAME (anything you want)
- 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!