Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Summarize with null entries

Fellow friends! 


I'm trying to solve this question, but unfortunately I didn't figured out how to do that.


I have this input:


Customer NamePurchase OrderInvoice Date


Note that I have blank cells and they are my information that purchase order still pending, or in other name, they're "open".


If they are blank cells in invoice date, purchase order grouping should be blank, this shows me that we didn't send all the purchase orders items, and the status will be Open

If there are dates in invoice date, should bring the maximum/last date and the status will be Closed.


This is the output that I'm expecting:


Customer NamePurchase OrderInvoice DateStatus
Smith2 Open
Alice3 Open


I have tried using summarize tool and grouping by purchase order and using max invoice date, but if I have both blank cells and date in a purchase order group, I got the max date when should be blank.

File attached. If someone has a solution/tip for this case I will be glad!





Here you go!


First I created a new column called Nulls with a formula that set the value to one or zero based on if the invoice date existed. Then I used a summarize to group by Customer Name and Purchase order and then summed this null column. By doing this, I was able to see which purchase orders had null values that existed. Then by using another formula tool I could easily say if the sum of the nulls was greater than zero, set the status to Open. 


I used another summarize tool to get the max invoice date of each purchase order and then joined this back into the stream. Then I used one final formula tool that said if the status was open, set the invoice date to null. Finally I sorted by purchase order number and everything looks good to go!


Here is the sample workflow.  Hope this is helpful.