Carve up a file based on row index

[franc1s]

I am trying to carve up one big text file that has trial balances for multiple entities. The goal is to read that one big file, and write each trial balance to its own file.

I have added a row index for each row in the "master" file. I can also dynamically generate a table that tells me the row at which each entities data starts,

My first idea was to somehow combine this table with the master file so that I could tag rows with entity id A/B/C through a formula tool. I could then generate a filename for each entity id and store the appropriate rows in each file with an output tool.

But, I dont know how to combine the data from the master file and my index table such that I am able to do what I want. As always, I'd appreciate the Communities help.

[DataNath]

Hey @franc1s, I have a feeling there may be a more streamline approach here but if so it's not coming to my head! I've gone down the route of using the lookup/data starts table to generate rows of all the ids of each entity_id. From there, we can conduct a join of this against the master data set to reflect which entity_id belongs to each row.

The top Generate Rows tool in my workflow is just generating 1,000 records for the sake of demonstration but this ought to be fully dynamic to any number of rows/entity ids.

[usmanbashir]

@franc1s - see attached workflow. Hope this helps!

 

[franc1s · Accepted solution]

@DataNath @usmanbashir you gave me some good ideas! Sometimes its starting you in the face, but, you dont see it. In the end its quite compact.

Joining the first occurence of the entity_id on row_id sets things up.

Then, I just have to copy down entity_id...

Thanks!!

[usmanbashir]

@franc1s - glad it worked! if you don't mind, could you mark the either/both responses as solutions! It's greatly appreciated!