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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

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
Mike108-28-2018
Mike108-25-2018
Mike108-31-2018
Smith208-27-2018
Smith2 
Alice3 
Alice3 
Alice308-31-2018

 

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

Rules:

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
Mike108-31-2018Closed
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!

 

Thanks. 

 

Comet

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!

Quasar

Here is the sample workflow.  Hope this is helpful. 

 

workflow.PNGWorkflowResults.PNGResults

Labels