Create groups with overlapping rows

Hello everyone,

I am looking for a way to create groups of rows based on the overlapping of rows. I know this sounds confusing, but stay with me. Let's look at an example: I want to create groups of 5 rows per group. The groups should consist of the follow rows:

Group 1: Row 1 to Row 5

Group 2: Row 2 to Row 6

Group 3: Row 3 to Row 7

...

Group n: Row n to Row n+4

(With the last group containing the last 5 rows of the dataset)

The groups can be shows by an identifier of a new field.

Any help would be much appreciated.

Workflow

Custom Tools

Macros

Iterative Macro

Accepted answers

BrandonB

I think that this should be a good starting point. I have built an iterative macro that accomplishes this. Iterative macro is attached!

Group Iterations.yxmc

iteration.yxzp

All comments

BrandonB

If you just need to do a calculation with a "rolling window" so to speak, you can probably use the Multi-Row tool: https://help.alteryx.com/current/designer/multi-row-formula-tool

If you are trying to duplicate the rows so that you have a full "group" and then the next group starts on that second set of numbers, you will probably be looking at an iterative macro to do so. Can you give a bit more information about the desired structure of your final output?

echuong1

You can use the multi-row formula to create the groups. I assigned each record a value of 1. I used the multi-row to add 1 if the value is less than 5. If the value is 5 it will restart at 1.

New Workflow1.yxmd

jgv27

@BrandonB - Thank you for the quick feedback.

I also thought of using an iterative macro. I had troubles using the iteration number as an input to change the Select Records tool's selection. I am also unsure how to stop the macro when the last group has the desired number of rows in the group. If possible, I would also like to have the group size as a variable.

The desired output is to "duplicate" the rows. There will therefore be rows repeated in the groups.

The attached photo shows the desired input and output.

Please let me know if you need more clarification.

Group Rows Example.PNG

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703