Idenitfy the dates

[Ronal_bal]

Hi ,

I'd like to identify the date of first negative value on the field BOH. And I need to capture the date of subsequent positive value on field BOH. I need to repeat this two times.

Attached sample:

1st impact date - 2025-06-04

1st recovery date - 2025-06-18

2nd impact date - 2025-06-28. If there are no values, then Null.

2nd recovery date - date value which has subsequent postive BOH. If not, pick the final date value in the group.

[Hsandness]

@Ronal_bal I got the first part done. I have to run to a meeting quickly and can complete the second part of the workflow when I'm back if you haven't solved it by then.

I think this workflow will get you the start you need. 

[acotta17]

Hi Ronal_bal,

You can achieve the desired result using the Multi-Row Formula tool. In this approach, we create a new column that generates outputs like:

1 impact

1 recovery

2 impact ...and so on, corresponding to each date.

Fields to create:
[Cycle] – tracks the impact/recovery pair number.
[Date_Tag] – generates the tag (like 1 impact, 1 recovery, etc.)

Formula 1- Cycle (Int32)
IF [Row-1:BOH] >= 0 AND [BOH] < 0 THEN
[Row-1:Cycle] + 1
ELSE
[Row-1:Cycle]
ENDIF

Formula 2 - Date_Tag (String)
IF [BOH] < 0 AND [Row-1:BOH] >= 0 THEN
TOSTRING([Cycle]) + " impact"
ELSEIF [BOH] >= 0 AND [Row-1:BOH] < 0 THEN
TOSTRING([Cycle]) + " recovery"
ELSE
NULL()
ENDIF

You can filter out the non-blank values in the Date_Tag column to get your desired output.

Let me know if this works.

[DavidSkaife]

Hi @Ronal_bal 

Bit of a long winded way of doing it, but appears to work.

Use a Multi-Row tool to generate the dates matching the criteria you have set out, and filter out any null rows to leave you with just those identified. IF the count of these rows is an odd number, identify the last row/date and join it back onto the original dataset. IF the count of the rows is an even number then no additional joins will happen.