Create groups with overlapping rows

[jgv27]

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.

[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.

[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.

[BrandonB · Accepted solution]

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

 

 

[jgv27]

Thank you very much. It seems to be exactly what I needed.

[BrandonB]

The macro that I built continues to create groups until the very last record. You may want to include a little bit more logic to filter out the groups that don't have the full group size desired, but it is otherwise exactly what you were describing.

[BrandonB]

This could easily be done with something like this