topic Re: how to Group by two columns and output min/max date using multiRow formula in Alteryx Designer Desktop Discussions

how to Group by two columns and output min/max date using multiRow formula

rajenlodh — Fri, 11 Oct 2019 05:25:23 GMT

Hi there,

need some help on the tool Multi row formula.

i got data where need to group by Application reference,then milestone names and select min of started on in one columns and max of completed on in another columns with Multirow formula tool.

i can do it by crosstab but want to use it via Multirow formula.anybody can help please.

simple task but not sure how to do without [summarize ->crosstab] use.

intend to use multirowformual.

if any other way to do happy to learn also.

Re: how to Group by two columns and output min/max date using multiRow formula

BrandonB — Fri, 11 Oct 2019 06:25:36 GMT

Could you share what your desired output would be? It looks like in your data you have multiple rows for a given application reference, but your milestone names are unique for each application reference group. Which milestone name would you want to keep?

Re: how to Group by two columns and output min/max date using multiRow formula

BrandonB — Fri, 11 Oct 2019 06:28:59 GMT

If you just need the min and max dates of the two columns and keep the application reference and milestone names as they are, the best way is probably to do a summarize where you group by the application reference, do a max of the column you need the max for a a min of the column you need a min for. Then join this data back to your original data on the application reference field and uncheck the columns of the old date fields just leaving the min and max columns to take their place.

Re: how to Group by two columns and output min/max date using multiRow formula

rajenlodh — Fri, 11 Oct 2019 06:41:50 GMT

Want to keep all milestone names but min/ max of dates

Re: how to Group by two columns and output min/max date using multiRow formula

rajenlodh — Fri, 11 Oct 2019 06:43:50 GMT

Hi mate, yes I know and use that but want to implement it via multi row
formula

Re: how to Group by two columns and output min/max date using multiRow formula

BrandonB — Fri, 11 Oct 2019 06:49:33 GMT

You can technically do it with a multi row. First use a sort tool to sort by application reference and then by the first date you are interested. Make sure that you do ascending or descending based on which value you want on top. Then use a multi row tool and set non existent rows to null. For your formula say iif(isNull(row-1:date),date,row-1:date)

This means it will keep the top date and set every following row to that date. Check the box to group by application reference so it does this for each block of rows for each application reference.

Then sort by your next date field in the order you need and repeat this process with another multi row tool using the same formula as explained above.

Re: how to Group by two columns and output min/max date using multiRow formula

rajenlodh — Fri, 11 Oct 2019 06:54:50 GMT

Thanks mate for the super explanation.is it possible to share with a
workflow please.
Will wait for your reply

Re: how to Group by two columns and output min/max date using multiRow formula

BrandonB — Fri, 11 Oct 2019 06:56:26 GMT

Currently typing from my phone but I can send one tomorrow when I’m back in the office. Should be a fairly quick workflow to build unless someone else beats me to it!

Re: how to Group by two columns and output min/max date using multiRow formula

rajenlodh — Fri, 11 Oct 2019 07:15:50 GMT

Thanks for your reply mate

Re: how to Group by two columns and output min/max date using multiRow formula

BrandonB — Fri, 11 Oct 2019 15:14:01 GMT

Workflow example is attached. Basically just sorting the dates then multi row to set the earliest or latest date to each row.

Re: how to Group by two columns and output min/max date using multiRow formula

rajenlodh — Mon, 14 Oct 2019 23:38:57 GMT

Hi Brandon, thanks for the reply and the flow,Appreciate so much your effort on it.

the flow looks promising pretty much, but before min max of dates, I need them group by Application_reference, then group by Milestone_names.

In the flow no sorting or used of Milestone names in the multiple row formula.

As i need to use it what should i do?

Re: how to Group by two columns and output min/max date using multiRow formula

BrandonB — Mon, 14 Oct 2019 23:21:48 GMT

If you look at the configuration in the multi row tools I have it grouped by the application reference field. This means that the formula will only apply to each set of application reference numbers. The reason that I did not check the box for milestone name is because row 1 is "Type A Closed" and row 2 is "Type B Closed" for the same application reference. If I checked the box for milestone name it would not apply the actual started on date to each row in the application reference block, but rather only to rows with that specific Milestone Name within the Application reference. You can definitely use multiple group by fields, but in this case we just checked the box for the first one because we want to apply the min and max to every row within the same application reference value.

Re: how to Group by two columns and output min/max date using multiRow formula

rajenlodh — Mon, 14 Oct 2019 23:42:46 GMT

got it mate.:)

so as i need to group by Application_reference, then by Milestone name for min/max of dates what can I do? any solution please

Re: how to Group by two columns and output min/max date using multiRow formula

rajenlodh — Tue, 15 Oct 2019 23:36:10 GMT

tried to write a code to find the minimum for each app_ref_then group by milestone name.

attached screesnshot & code here

if(([Application_Reference]=[Row+1:Application_Reference])
and ([Milestone_Name]=[Row+1:Milestone_Name]))
then

if(([Application_Reference]=[Row-1:Application_Reference])
and ([Milestone_Name]=[Row-1:Milestone_Name])) then
min([Actual_Started_On],[Row+1:Actual_Started_On],[Row-1:Actual_Started_On],[Row-1:min_started_On])
else
min([Actual_Started_On],[Row+1:Actual_Started_On])
endif

elseif(([Application_Reference]=[Row-1:Application_Reference])
and ([Milestone_Name]=[Row-1:Milestone_Name]))
then
min([Actual_Started_On],[Row-1:min_started_On])

else
[Actual_Started_On]
endif

plus from the flow you shared, cna do with soritng by app_ref, then milestone_name, then sort by startedon date ascending.

then on the running total instead of only App_ref, used both app ref,milesonename.