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

Count the non-null values across multiple columns?

himaniyadav
6 - Meteoroid

Hi all,

 

I would like to count the number of non-null values in each row across multiple groups of multiple columns. Basically, for example, my data set might look something like this:

 

Name          Q1               Q2            Q3             A1          A2         

John D.       10.2             2.5             31            12           null

Jane Y.        null             10.5          null            45.5        12.2               

Jack A.        5                  7.5           12             null          null

Jill R.           7                 null            7              2.5          10 

And I would like to obtain something maybe like this:

 

Name          Q1               Q2            Q3             A1          A2         QCount         ACount

John D.       10.2             2.5             31            12           null             3               1

Jane Y.        null             10.5          null            45.5        12.2            1                2   

Jack A.        5                  7.5           12             null          null             3                0

Jill R.           7                 null            7              2.5          10                2                2

I am still pretty new to Alteryx so I'm not sure where to begin (summarize, formulas?). Any help at all would be much appreciated!

4 REPLIES 4
LordNeilLord
15 - Aurora

Hey @himaniyadav 

 

In these situations you should start by transposing the data and counting the values, it makes working with the data much easier.

 

I have attached an example

MichalM
Alteryx
Alteryx

@himaniyadav 

 

Transposing the data will be step one and then you could take number of paths. This is one of the options

 

count-non-null.png

himaniyadav
6 - Meteoroid

Thank you both. In reality my dataset is a lot more complicated but these examples were a very helpful starting point.

ChrisTX
15 - Aurora

The attached workflow gives the correct result for your sample data.  The heavy lifting is done by Transpose and Crosstab.

 

To maintain the correct field ordering, I used the Field Info tool then the Dynamic Rename after the Crosstab.

 

Here's a related post, about re-ordering columns after a Crosstab: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-order-columns-by-any-given-line...

 

Capture.JPG

 

Capture2.JPG

 

Chris

Labels