Insert new row every nth row.
- 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
I have 2 columns of data. first column is a string and the second is numbers. i can sometimes have upwards of 3000 rows. However, my final output will be excel files and i can only have 900 lines per file. So every i need to insert a new row at every 900th row and find the total of all the numbers that came before it. Also, the total number of rows i will have will never be an exact multiple of 900. So I'm wondering if there is a way to insert a new row every 900 and dynamically insert a row at the very end of my data?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This is one approach
I used a mod function to determine the remainder on the largest number of the group. In my case I set it to 6, but in your case would be 900 or 899 depending on whether you count the total. Then I used a multi-row tool to create an actual group number because when mod([RecordID], 6) = 0 that means that it is divisible by 6 with no remainder and therefore the last number in the group so the multi row increments to the next group number. Then I feed it into a batch macro that sum totals the values in the group being fed in. Workflow is attached. My other thought was to avoid a macro and use a running total grouping by a given group but then you end up doing some manipulation on getting it under the last row of each group so I figured this was the most straightforward.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Charlie_J
Just a sample. Maybe there is a better way. 😁
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Charlie- If the objective is to simply output 900 records per Excel file, you may consider an alternate approach. The Output Data Tool has options that allows you take file/table name from a field within your dataset. Check this box and use the Change Entire File Path selecting a field that has your generated path for the Excel files.
To Generate the Directory Path per row you will need to do the following steps:
- Add a Record ID Tool to your workflow
- Add a Formula Tool to your workflow to generate a string field called Grouping (Expression: CEIL([RECORDID]/900))
- Add a second field to the Formula tool that generates the directory path (Expression: "C:\Folder\"+[GROUPING]+".xlsx///Data")
- [Optional] Add a Select Tool to remove the GROUPING Column if you only want two columns in your output data set.
Goodluck!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
