Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Generating Counts of the Combo of Fields with NULLs

whitkrieng
8 - Asteroid

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: 

LastNameEmailAddressPhone
Smithsmith@alteryx.com123 Somewhere St.NULL
NULLNULLNULL555-555-5555
HarambeNULLNULL123-333-3333
NULLNULL111 Sesame Place122-222-2222
NULLNULLNULL111-222-3333

 

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

 

Field CombosNULL_Counts
LastName/Email/Address2
LastName/Email1
Email/Address1
Phone1

 

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. 

5 REPLIES 5
IraWatt
17 - Castor
17 - Castor

Hey @whitkrieng,

Here is a workflow which can achieve your desired output:

IraWatt_1-1654785946687.png

 

Any questions or issues please ask :)
HTH!
Ira

 

ddiesel
13 - Pulsar
13 - Pulsar

Hi @whitkrieng!

 

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

 

Capture.JPG

 

Let us know if these solutions work for you.

 

Thanks,
Deb

whitkrieng
8 - Asteroid

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
9 - Comet

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

 

Capture.PNG

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

ddiesel
13 - Pulsar
13 - Pulsar

Hi again @whitkrieng!

 

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

 

Capture.JPG

 

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

 

Thanks,
Deb

Labels