Generating Counts of the Combo of Fields with NULLs

[whitkrieng]

Hi all,

I'm trying to find an efficient way of trying to find the count of records of the associated fields that have NULLs together. 

My dataset is based on a certain number of Personal Traits; here is an example sample: 

LastName	Email	Address	Phone
Smith	smith@alteryx.com	123 Somewhere St.	NULL
NULL	NULL	NULL	555-555-5555
Harambe	NULL	NULL	123-333-3333
NULL	NULL	111 Sesame Place	122-222-2222
NULL	NULL	NULL	111-222-3333

So what I want the output or results to show me is that: 

Field Combos	NULL_Counts
LastName/Email/Address	2
LastName/Email	1
Email/Address	1
Phone	1

I want to get an idea which combo of fields has the most # of records with NULLs, and which combos may have likely to be NULL together.  

Like just from observational purposes it seems like If LastName is NULL, Email is most likely NULL. 

Thanks everyone for your help, lmk if you have any questions. 

[IraWatt · Accepted solution]

Hey @whitkrieng,

Here is a workflow which can achieve your desired output:

Any questions or issues please ask :)
HTH!
Ira

[ddiesel · Accepted solution]

Hi @whitkrieng!

Here's an alternate approach that is very similar to @IraWatt's suggestion:

Let us know if these solutions work for you.

Thanks,
Deb

[whitkrieng]

Thank you both for these solutions! The Transpose appears to be integral to the workflow, I have to think about how this will affect processing time when say my whole dataset is around ~13M records and growing, hence this could blow up when the data is nomralized.  If there is a workaround to avoiding transpose, I'm open to it.  Thank you again! 

[Matt_D]

@whitkrieng this is without Transpose Tool (kind of)

It does use it but only to transpose the first record to create the formula, hope this helps.

Should be dynamic too if you need to verify more fields.

[ddiesel]

Hi again @whitkrieng!

Here is another possible approach utilizing the Formula tool and Summarize tool only.

Please let us know if one of these solutions work for you.

Thanks,
Deb