Add a column for an end date in MultiRow Tool

[alphabex18]

Hi all, 

I want to create a new column called "PlanEndDate" in this workflow. Below are my fields. the EEPID field is unique, and repeats for every time a plan is changed. The PlanEffectiveDateHistory Field is the "start date" of that plan, but i need to calculate the end date. I cant get my if statment to work correctly in my MultiRow tool. Here is what I want to accomplish in that tool

IF ([Row+1:EEPID] = [EEPID] then Row+1 PlanEffeftiveDateHistory minus one day. If the EEPID row is the last one then it would populate with the last day of the current month or something that would indicate that the plan is active and has not ended. This would be a date field, so putting "current" in the field probably wouldn't work

for example records 1-4 is the same person, and here is the desired outcome for the new field

Record	EEPID	EmployeeEnrollmentDate	PlanEffDateHistory	PlanEndDate
1	01005.087.M	2006-04-01	2006-04-01	2009-03-31
2	01005.087.M	2006-04-01	2009-04-01	2014-11-30
3	01005.087.M	2006-04-01	2014-12-01	2017-11-30
4	01005.087.M	2006-04-01	2017-12-01	current plan or some kind of date that could always indicate that its a current plan. maybe 2222-01-01

Here is my sample data

[carlosteixeira · Accepted solution]

Hi @alphabex18 

Try use this workflow adpting to your necessary. If not work or you can´t understand, let me know and send your source and so that I can ride for you.

Holpe this help you.

Best regards

[alphabex18]

its helpful for sure but I think my iif statement is malformed, and i dont know how to get it right. This is what i have...

iif([Row+1:EEPID]=[EEPID], DateTimeAdd([Row+1:PlanEffectiveDateHistory],-1,"days")

[carlosteixeira]

Can you put here your workflow?

This way my help will be more efective! :-)

[alphabex18]

Nevermind. I got it. it was actually much easier than I first thought

Here is the final result for anyone else that comes across this problem....

IF [Row+1:EEPID] = [EEPID] THEN DateTimeAdd([Row+1:PlanEffectiveDateHistory],-1,"days") ELSE "2222-12-02" ENDIF

[carlosteixeira]

Good Job my friend,

If you need more help dont hesitate let me know.

Best Regards