Skip to main content
Solved

Output a single comma delineated attribute from a list of empty/missing attributes grouped by a PK


bwasserstein
Participant
Forum|alt.badge.img

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).

PKFKAttribute1Attribute2Attribute31A

 

DATADATA2ADATA

 

 

3BDATA

 

DATA4BDATADATADATA

 

So the output would look like this...

PKFKList_of_null_attributes1AAttribute12AAttribute2, Attribute33BAttribute24B

 

 

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.

Best answer by ebygomm

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

View original
Did this help you find an answer to your question?

2 replies

ebygomm
Influencer
Forum|alt.badge.img+32
  • Influencer
  • Best Answer
  • July 19, 2019

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


bwasserstein
Participant
Forum|alt.badge.img
  • Author
  • Participant
  • July 19, 2019
ebygomm wrote:

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. :)


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings