Consecutive dates until condition is met

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

Designer Cloud

Accepted answers

Adrian_T

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

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.

Buffer dates until flag is reached.yxmd

ShankerV

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

Solution 2.yxmd

Solution 1.yxmd

All comments

Adrian_T

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

Buffer dates until flag is reached.yxmd

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

stop at flag.yxmd

ShankerV

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!!!!