Partition by event a logical set of values

[aden08]

Hello community, I hope you can help me with this challenge. I want to partition by event, as you can see in the image (output), for a sequence of values. As you can see, the value '1' appears in different sequences throughout the results, and it is these values (1, 2, 3, and 4) that I want to extract. Thank you very much.

[cjaneczko]

Are you looking to count or sum the values by partition? The summarize tool should get you where you want by using Group by on Output Event.

[aden08]

no, What Im looking for is to generate the values 1, 2, 3, 4, n... in the "Event" column based on the sequence, as shown in the example.

[Clifford_Coon]

Hi @aden08 ,

You can use a multi line formula:

IF isnull([Value]) THEN null()
ELSEIF [Sequence] = 1 THEN [Row-1:Output Event]+1
ELSEIF !isnull([Row-1:Value]) THEN [Row-1:Output Event]
ELSEIF isnull([Row-2:Value]) THEN [Row-3:Output Event]+1
ELSEIF isnull([Row-1:Value]) THEN [Row-2:Output Event]+1
ELSE [Row-1:Output Event] ENDIF

Happy Solving :-)

 

[aden08]

Thank you very much for your input, Cliford. It's a great approach. The issue is that my sequence of continuous '1' values doesn't necessarily start at 1; it can start at any number. Additionally, the number of consecutive values is not always 4; it can be any number. Here's an excerpt of the data, along with an example of what can occur. This is filter for non null Values.

[binuacs]

@aden08 what is the logic you applied here to find the output? 

[aden08 · Accepted solution]

I also completed the task using the Multi-Row Formula and RecordID functions. I separated the null values of the 'crit' field and added the 'event' values using the Multi-Row Formula. Then, I assigned the number of the 'event' to the null values using the RecordID. Finally, I used the Multi-Row Formula again to populate the 'recordID' field and matched it with the 'event' field. These are the desired results