date and data referencing

[COrr]

So I'm trying to figure out a road block here.  I have created these fake rows of data here to help figure out my issue.  So I have these phantom employees and types of leave they were on.  Some of these event actions have an event end date of 12/31/9999 to represent they are on a current leave.  But some of these employees have event actions that also contain a "Return to work" or "Retired".  I would like to remove these event action and reference the previous action that the employee either "Return to work" or "Retired". Below is  a before and after of how I need the data to look. I also added both in the workflow I attached. I wasn't sure if a Multi-field formula tool would work here or not.

[hanykowska · Accepted solution]

 Hi @COrr 

I used multirow formula and filter combo:

(needed to add a select tool for the data not to be truncated)

It would be interesting to see if it still works for more data. At the moment I made a condition based on whether the Reason field contained 'PAID' element, it could be better if you had a flag for whether the event was a leave or something else

IF !Contains([Row+1:Reason],'PAID',1) AND !Isempty([Row+1:Reason])
then [Reason] + ' (' + [Row+1:Reason] + ')'
else [Reason]
endif

Here's the output I got (the employees are in a different order but that shouldn't be a problem)

Let me know if this works and if you have any further questions!

[COrr]

dude this is exactly what I'm looking for thanks!!!!!

[hanykowska]

The magic of alteryx 😉

you're welcome!