Output a single comma delineated attribute from a list of empty/missing attributes grouped by a PK
I've got an excel spreadsheet of data with a primary key column, a foreign key column, then various other columns that may or may not contain data. I'm trying to create a single attribute that lists all of the columns/attributes that do not contain data for a given PK/FK combination (i.e., are null/empty/missing).
| PK | FK | Attribute1 | Attribute2 | Attribute3 |
| 1 | A | DATA | DATA | |
| 2 | A | DATA | ||
| 3 | B | DATA | DATA | |
| 4 | B | DATA | DATA | DATA |
So the output would look like this...
| PK | FK | List_of_null_attributes |
| 1 | A | Attribute1 |
| 2 | A | Attribute2, Attribute3 |
| 3 | B | Attribute2 |
| 4 | B |
Any ideas? I can't seem to wrap my head around this.
My first thought was to attribute all null/missing/empty attribute values with a unique identifier, then replace all real data values with an empty field so that I can drop those attributes with a ListConcatenator.
Right now my workflow for that includes a NullAttributeMapper to replace null/missing/empty values with a specific unique character (lets say "X"), a stringreplacer & regex to replace all data values except an exact match with that specific unique character (^(?!X$).*$ if I use "X" as my specific unique character), then a ListBuilder/ListConcatenator (grouped by PK, FK with drop empty & null elements set) to bring it all into one attribute for each PK/FK combination. There's likely at least couple flaws in my workflow...it's not workin' out!
So now I'm here asking for help, but I'll keep working on a solution and post it here if I find one.
Thanks for your time.
An attribute validator testing for has value will add a list of all attributes that fail the check with the value "Attribute 'Attribute2' fails check for Has a Value".
You can then explode the list, extract the attribute names and then aggregate them into a comma separated value
Workflow
Output
Thank you enormously, egomm. I was able to set up two different workflows that provide the same output (based off your initial suggestions & off your posted workflow).
I had never used the AttributeValidator, and I needed a reminder as to how powerful ListExploders and Aggregators can be.
Thanks again for your expertise. :)
