Idenitfy the dates

Question

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.

test_impact.csv

davidskaife · Answer

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.

Dates.yxmd

acotta17 · Answer

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.

Hsandness · Answer

@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.

Identify the Dates.yxmd