Partition by event a logical set of values
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 :-)
 
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@aden08 what is the logic you applied here to find the output?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
