community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Group By and Sort with NULL Values Last

Meteor

Is there an easy way to do a group by and sort so that the NULL values go to the end of each group rather than the beginning?  Example via SQL:

 

select col
from table
group by col
order by col NULLS LAST

Alteryx Certified Partner
Alteryx Certified Partner

@mark_cyr,

 

Quick thought (might work):

 

Create a field:  IsNull as:

IIF(IsNull([col]),1,0)

Sort by:  IsNull (Ascending), Col (Ascending)

 

Use a SELECT to get rid of the IsNull Column.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

I forgot to mention that I'm trying to do this across a large number of columns.  There are nulls present in several of them.  

 

I hacked together something similar to your idea @MarqueeCrew. I used the multi-field formula tool and and replaced all NULL value across all of those columns with a fixed value that would sort last (depending on whether it was alpha or numeric).  I then did a group by / sort. After that, I used the multi-field formula again to replace the fixed value with NULL again. I don't like doing this like this and changing my input data, but the idea you suggested would require a number of helper columns.  

Labels