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!
Solved! Go to Solution.
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
Transposing the data will be step one and then you could take number of paths. This is one of the options
Thank you both. In reality my dataset is a lot more complicated but these examples were a very helpful starting point.
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...
Chris