how to choose whole line of each column

[lilyyangadsk]

can advise if i pull opportunity from SFDC, how to choose the whole line of information that meet below criteria:

Choose SFDC opportunity with same Account Name that  latest project closed date if Actual close date is null or latest Actual Close date if not null

OR 

Choose SFDC opportunity with same Account Name that if Project close date is equal then choose most recent created date.

thank you very much!

[grazitti_sapna]

Hi @lilyyangadsk, can you provide some sample data?

Thanks!

[lilyyangadsk]

here attached sample data and column a marked the line i want to pick by alteryx thankyou. 

[grazitti_sapna]

Hi @lilyyangadsk, here is the workflow for your problem statement. I could not test it on the larger dataset but it works for the dataset that you provided.

I hope this helps!

Thanks!

[DawnDuong · Accepted solution]

hi @lilyyangadsk 

One way to approach this issue to arrange the data in a rank-ordered fashion and then select the 1st record for each group of Account name. As in all sorting problem, the sort sequence is critical.

1. From reading your problem statement, it appears that the sorting sequence is:
   1. "Account Name", either descending or ascending will be fine > this first step groups your data by account name.
   2. "Actual Close Date" Descending Sort. > this 2nd step does 2 things. First all projects which have an actual closed date will be sorted above those without (i.e. null) actual closed date. Within those with an actual closed date, the most recent is pushed on top.
   3. "Projected Close Date" Descending Sort.
   4. "Created Date" Descending Sort. 
   5. Before you do the sorting however, you need to make sure that the date columns ("Actual Close Date", "Projected Close Date", "Created Date") are formatted as "datetime" datatype, otherwise your sorting results will be wrong. Do note that one of your records contains impossible date (31 April does not exist) so I've changed the source to 30 April.
2. After you have sorted the data, you can use the Sample tool to take the 1st record within each group with the same "Account Name"
3. Then the last step is just to pick the date of those selected records.

The following screenshot contains all the settings information, so I'm not attaching the workflow.  I'd highly recommend that you check out the Alteryx Interactive Lessons page for bite-sized tricks on how to maximise the use of all the "foundation" tools. I can confidently say that the 15 or so foundation tools are 90% sufficient to solve most basic finance or datablending issues such as this.

Happy exploring!

Dawn.

[lilyyangadsk · Accepted solution]

Could you share the workflow , thank you

[grazitti_sapna · Accepted solution]

@lilyyangadsk, I replicated the workflow of @DawnDuong.

Thanks!

[lilyyangadsk]

thank you and it seems quite easy to use this one. 

[lilyyangadsk · Accepted solution]

Thank you!! It is very practical to use.