Aggregate Multiple Text-based Columns to Card

[JPeG]

Hello Domo Community! 

Long time reader, first time poster... hoping someone out there might have a solution for a relative newbie to domo and using beastmode. 

The Sitch

I'm working with a file right now that will show pass/fail results across multiple columns. Each column represents a different category of failure, and the data in that column may either be blank (meaning non fail on that entry for that particular fail type), or it could have text. Here is the example ----->

Some of the 'fails' are due to a single category (column) some are multple. 

What do I wanna do? 

I would like to do one of two things with this data (a basic version, OR, a more advanced version) -- i'm MORE than content with the basic, but posting both in case anyone is feeling particularly codey today 😃

• Basic Version - I would like to aggregate the data in a way that i could create a visualization using a stream chart. (note, i do have dates as well). I'm hoping that i can total up each column to show failures categories over time. So, how many failures did we have due to "Spacing" (whether it was the only failure reason or not).

• Advanced Version - Some of these 'categories' don't actually make sense (the data is passed to me by another team. Each sub-code under a category (the A9 / A14 / etc.) might actually fall into different buckets for my team. I'd like to take a list of these A / B / C type codes, perhaps in a separate sheet, and re-bucket the categories based on these codes. So even though the master category is "Spacing", A9 may be a bucket of "Spacing between keys on keyboard" and A14 may be "spacing from keyboard to monitor". The "Spacing from keyboard to monitor" may also apply to B21 - so i want anything that's A14 or B21 to show up in that bucket. 

In both examples, the output would be to a stream chart to help show fail types over time - i.e. is a bucket growing, shrinking, etc. This works for a separate chart i put that shows our pass / fail rate overall, but i'm struggling to figure out how i can use multiple columns for series here. The stream chart only permits one 'series' column, so i'm sure i have to figure out how to aggregate the data... but - i'm not sure how to do that. 

Hopefully that description is enough info. Note: i'm not actually doing a QA program on spacing from keyboard to monitor... but if i did, i would say i'm way to close to my monitor 7 of 8 hours a day.... though i'll be glued to it to see if someone happens to have a solution for me. Appreciate any help that's out there. 🙂 

[MarkSnodgrass]

I would suggest you look into either of these options and see what makes the most sense for you. Both of them revolve around taking data from multiple columns and putting them in a single column.

Approach 1 - Use the Magic ETL Collapse Columns tile

https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/02ETL_Actions%3A_Edit_Columns#Coll...

This KB article does a nice job of outlining how you do this. You could create a new column called FailReason and pivot each reason to that column. It would also allow you to rename certain reasons, which you indicated you want to do.

Approach 2 - Create a beast mode called FailReason

You could create a beast mode that would essentially do the same thing except that you will run into challenges when someone has multiple failures because beast modes can't pivot data and you would have to concatenate multiple reasons. Your beast mode would be a long CASE statement that would look at each column and allow you to rename a reason. A simple example would look like this:

CASE WHEN LEFT('Fail1',2) = 'A1' THEN 'Spacing'
WHEN LEFT('Fail1',2) = 'A2' THEN 'Something else'
END

After writing this out, the Collapse Columns would be your best approach to ensure you can total each fail type individually.

[jaeW_at_Onyx]

@MarkSnodgrass  nailed it on the head.

build a lookup table that has one row for each failure code and how you want them remapped. (cols. FailReason, FailReason_CustomGroup)

then Magic + Collapse columns will be the easiest way to collapse your data.

then JOIN the lookup and the MagicETL column

to avoid inflating your failure counts 

(ex for one ID I could failed A14 and B21 on separate rows)

YOU CAN EITHER:

use a RANK & WINDOW tile to ROW_NUMBER() partitioned by ID, FailReason_CustomGroup

That way in your visulaization you can  filter by the first instance of each CustomGroup per ID.

OR 

use a GROUP BY tile to concatenate FailReason into one string partitioned by ID, FailReason_CustomGroup