Generating multiple rows

[Able4]

I need to generate new rows: 1) The information has an anniversary date, investor name and investor ID which i want to remain the same till the anniversary date is equal to the To date or when its equal to 31 December 2023 if the anniversary date is greater than 31 December 2023. I have included an excel sheet with one sheet having sample information and the other sheet having my desired output. The uniqueness is based on investor name and investor ID.

[Bren_Spill]

@Able4 - attached will get you started. You'll need to add some logic to update the Anniversary Date, but the rows are generated. Not quite sure I understand how the anniversary date logic is working in your example output.

[Able4]

@Bren_Spill thanks for this. The logic is more that when the anniversary date becomes equal to the To Date then the following months should get a new anniversary date. For my case investor Able with ID no of 1 should have anniversary date of 31/03/2023 till 31/03/2023 and thereafter get 31/03/2024. hence why he appears twice in the excel workbook i shared.

[Qiu]

@Able4 
Maybe you can show us how the "From Date" and "To Date" are generated for the case investor Able with ID no of 1?
Your Input is as below, and I could not link them together.

From Date	To date
2023/1/31	2023/2/28

[Able4]

@Qiu so the two dates are just dates from a system that capture when the anniversary dates should change ie. someone can have an anniversary date of 31/march/2023 then in April 2023 as he has already reached the anniversary date he is assigned a new anniversary date. Investor ID has 1 and 2 which signifies that these are two different investors.

[CoG · Accepted solution]

First ensure that you are working with Date Types in Alteryx, then, using the Formula Tool, you can update [Next Anniversary Lock Up Date] to be:

IF [To date] >= ToString(DateTimeYear([To date])) + Right([Next Anniversary Lock Up Date],6)
THEN ToString(DateTimeYear([To date])+1) + Right([Next Anniversary Lock Up Date],6)
ELSE
[Next Anniversary Lock Up Date]
ENDIF

^ You essentially check to see if the Anniversary (for that year of [To Date]) has already occurred by [To Date] and increment year by 1 if it has. Otherwise, leave the date alone. This will correctly update Anniversary date even if year is off by multiple years.

Hope this helps and Happy Solving!

[Qiu]

@Able4 Thanks for providing the information.

@CoG nice one! You are true master! 😁