Consecutive dates until condition is met

[K_tina]

Hi, I am new to Alteryx and would like to perform the following data manipulation. Any help is appreciated.

Sample of my data below. I need to add the number of days in “Buffer” to “Date” and stop the logic when “Flag” is marked as 1. I.e. no more calculations to be done after the first Flag = 1 is encountered.

How can I do a “loop” in Alteryx?

Thanks

 

[Adrian_T · Accepted solution]

Hey @K_tina,

This can be achieved using the DateTimeAdd Function and conditional statement in a Multi-Row Formula Tool. This thread (LINK) should give you a good idea of how to achieve this.

Hope this helps!

[DataNath · Accepted solution]

Interesting with the flag added in @K_tina - I've just added a running total to identify where the flag starts and then after that, used a formula to add the days if the flag hasn't yet been reached i.e. has a value of 0:

The Formula expression here may look a little crazy. That's because your incoming date is in dd/mm/yyyy format which Alteryx can't deal with, therefore we need to parse it out into ISO format (yyyy-mm-dd) so we can use DateTime functions on the result. I've then wrapped it in DateTimeFunction to return to your original format. The final select simply removes the temporary running total field that was used for the buffer logic.

[K_tina]

Okay thanks i will go check it out, thanks for the quick and comprehensive responses!

[DavidSkaife]

Hi @K_tina 

Here is one way of doing it:

A multi-row formula that creates a dummy field to filter out all records after the flag field, it then calculates the revised date using the fields provided, before Unioning the data streams back together and removing the dummy field created initially.

I've set it to include the row that has the flag on it, if you want it to stop on the row prior then change the formula in the Multi-row tool to the below:

IF [Flag] = 1 or [Row-1:Test] = 1 THEN 1
ELSE 0
ENDIF

[ShankerV · Accepted solution]

Hi @K_tina 

We can achieve the solution in many ways. One simple method to achieve this is below.

Solution 1:

Solution 2:

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[K_tina]

Thanks for the comprehensive solution @shankerV. I had to review my workflow again and made some tweaks using on your suggestions! 

[ShankerV]

Hi @K_tina 

Thanks, glad my solution helped.

Let me know if you have any more questions. 

Many thanks

Shanker V

[K_tina]

Hi ShankerV, I have a separate question on the email tool, are you able to help for that too? 

Thank you

[ShankerV]

Hi @K_tina 

To send emails, connection is required.

The below blog will help you to do the connection settings for Outlook, Gmail etc.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/SMTP-Configuration-of-Common-Email-...

Many thanks

Shanker V