Outputting a file into the last JE before a certain count of rows

Question

Hi - I am going into an ERP integration and attempting to split out the journal entries into the max row upload for the new system. The max row limit is 25,000 including the headers. I know how to break the files into 24,999 rows + headers, but I also need to make sure the last journal entry (JE) doesn't carry to the next file and appear in two files. Is there a way to make the output file stop at the next to last JE before hitting the row limit? This will mean each file will ultimately be less than the row limit to not have JE"s crossover files. Thanks!

LiIrving · Accepted Answer

Thank you for your solution; I ended up finding a solution before trying this out. My solution was:

I figured out the solution that worked.

1) Summarize the data counting the rows per JE

2) Create a running total on the JE count

3)Identify the largest JE

4)Take your max row limit and subtract the max count of JE rows (24,999 - 6624 = 18,375 in my case)

5) Create a formula for the groupings: Ceil ([RunTot_Count]/18375)

6) Join the Group back into the raw JE detail

7) Output the file via CSV and check the "Take file/Table name from Field" box, select "Append Suffix to File/Table Name" and choose your output field

* I also grouped by month so my Step 1 was group by posting period by JE # then count and I had a second formula to get my group after step 5. It was  ToString([postingperiod]) + ' ' + ToString([Group])