Partition by event a logical set of values

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.

Workflow

Custom Tools

Common Use Cases

Preparation

Accepted answers

aden08

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

All comments

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 :-)

Partition.yxmd

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.

binu_acs

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

aden08

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703