topic Re: Add a column for an end date in MultiRow Tool in Alteryx Designer Desktop Discussions

Add a column for an end date in MultiRow Tool

alphabex18 — Fri, 21 Jun 2019 16:16:27 GMT

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

Re: Add a column for an end date in MultiRow Tool

carlosteixeira — Fri, 21 Jun 2019 16:23:06 GMT

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

Re: Add a column for an end date in MultiRow Tool

alphabex18 — Fri, 21 Jun 2019 16:36:07 GMT

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")

Re: Add a column for an end date in MultiRow Tool

carlosteixeira — Fri, 21 Jun 2019 16:49:40 GMT

Can you put here your workflow?

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

Re: Add a column for an end date in MultiRow Tool

alphabex18 — Fri, 21 Jun 2019 16:50:44 GMT

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

Re: Add a column for an end date in MultiRow Tool

carlosteixeira — Fri, 21 Jun 2019 16:52:44 GMT

Good Job my friend,

If you need more help dont hesitate let me know.

Best Regards