Repeat a row every 25 records

[lahughes]

I have the following output above.  The Agreement Number is a header line signified by the H in the ID Column, that I have generated in the report.

I want that Agreement Number header to repeat after every 25 records in the group.

So if there would be 24 data records it would only appear once.  27 data records though, it would appear twice.  Etc.

There are multiple agreement numbers in the report so each header line is unique for each grouping.  I can get a single line of it at the start of a new Agreement Number but can't figure out how to make it repeat after every 25 records within that grouping.

Appreciate any help.

Thanks,

Larry

[Thableaus]

Hi @lahughes 

Here's the solution I came up with

- Add a RecordID to keep track of the counting of your records;

- Use a Mod Formula to identify the rows you want to repeat agreement number (in this case, if the division of your RecordID by 25 leaves a mod of 1 - this will happen every 25 records, records come out in the True output)

- Apply a Multi-Row Formula tool to fill out the empty rows you need with your agreement number.

- Union these records back to your dataset

- Sort the Records to the original order.

Cheers,

[lahughes]

@Thableaus 

Thank you for your ideas.  The only problem with that idea is that the header row you see is one I created and inserted into the data as an extra row.

I did that in the same manner you used, in terms of I first found all of the unique Agreement Numbers, then used a formula tool to add an H to each of those for my ID Column, then Union those records back to the main data.  This created my extra header rows and I used a sort to put them at the top of each Agreement Number grouping.  Lastly used a formula tool to clear out the agreement number from any of the data fields, so that the Agreement number was only present in the header row.

So the problem is I actually have to insert a new row after every 25 records for a given agreement number.  And that new row then needs the agreement number, the H in the column ID, and all other fields would just be null.

If I am looking at your flow correctly, it identifies the current record id every 25, but I really need to insert a new row at that point, and not just use the current row data if that makes sense.

Does that additional info help?

[DavidP · Accepted solution]

Hi @lahughes,

My approach is not dissimilar to @Thableaus. I also use the Mod() function in a multi-row formula. It creates a RowID, but skips 25,50,75, etc. I then use the Summarize tool to find out how many header rows need to be inserted to the data and creates the RowID's for them, i.e. 0,25,51,76, etc.

In my case I just append a single header row to these Header RowID's, but you can create a list of Header Rows and join then to your Header RowID's by record position.

Finally the 2 sets of data are unioned and sorted by RowID.

[Thableaus]

@lahughes oh, I get it.

I think @DavidP's solution will come in handy. Besides, it's very smart and concise. I'd go this way.

Cheers,

[lahughes]

Thank you David and Thableaus,

Between both your workflows I was able to get the workflow to do exactly what I needed. Appreciate all the help!!

Thanks you.

[DavidP]

No worries @lahughes, glad we could help out. There's nothing stopping you marking both suggestions as solutions if both helped you achieve a resolution to your problem. @Thableaus did all the hard work upfront!