Skip to main content
  • EN

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.

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

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

Reply


Community Stats

30,159
Posts
114,480
Replies
37,806
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.