In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Partition by event a logical set of values

aden08
9 - Comet

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.

aden08_0-1687371567719.png

 

6 REPLIES 6
cjaneczko
13 - Pulsar

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
9 - Comet

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
11 - Bolide

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

 

partition.jpg

 

Happy Solving :-)

 

aden08
9 - Comet

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.

aden08_0-1687375403717.png

 

binuacs
21 - Polaris

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

aden08
9 - Comet

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

aden08_0-1687437232034.png

aden08_1-1687437321126.png

 

 

Labels
Top Solution Authors