Multi Row Formula and loop

[Anastasio_Theohari]

Hello Guys,

Lets say we have an input like this

ID	Status	StartDate	EndDate
20000265	Active	20001114
20000265	Active	20150225
20000265	Active	20160330
20000265	Active	20160819
20000265	Active	20170519
20000265	Active	20171129
20000265	Terminated	20210205

When i am taking Terminated status and value i want to fill all the previous values with the Terminated_Date-1

So the diserable output will be :

ID	Status	StartDate	EndDate
20000265	Active	20001114	20210204
20000265	Active	20150225	20210204
20000265	Active	20160330	20210204
20000265	Active	20160819	20210204
20000265	Active	20170519	20210204
20000265	Active	20171129	20210204
20000265	Terminated	20210205

Th MultiRowFormula works good with the following exrpession :

if ISNULL([EndDate]) and [Row+1:Status]='Active' and [Row+2:Status]='Active' and [Row+3:Status]='Active' and [Row+4:Status]='Active' and [Row+5:Status]='Terminated' then Left(DateTimeAdd([Row+5:TerminationDate_date],-1,'Days'),10)

The case here is that i dont know the number of Active i will have when i  take Terminated,so maybe i will more than 10 records or just 3,4 whatever..

So,do you know any solution to loop all the Active, group by the ID and fill with TerminationDate-1 if i receive Terminated status...

Thank you

[Emil_Kos]

Hi @Anastasio_Theohari,

I have created a workflow for you:

The output:

[AngelosPachis · Accepted solution]

Hi @Anastasio_Theohari ,

Maybe you can avoid the multi-row formula tool and use a join tool instead to bring the End date for Terminated Status for each ID?

EDIT : Looking at Qiu's solution, I realised I forgot to subtract one day from the terminated date. Thanks @Qiu 

Hope that helps, let me know if that worked for you please.

Cheers

Angelos

[Qiu · Accepted solution]

@Anastasio_Theohari 

Something dynamic for you.

[Anastasio_Theohari]

Thank you my friend