Splitting Records dynamically, based on a column values

[anujaymathur]

Hey,

I have this huge dataset of more than 80GB, that has transactional data of more than 10 years.

I need to create smaller extracts - one for each year (10 extracts in total), so that it is easier for me to run workflows.

As of now, I am using a series of Filter tools - which basically means I have to put about 9 tools to get the desired result.

Is there a way I can do this using a single tool?

While we are at it, Can i use multiple columns to partition data? e.g - 1 extract for all transaction through Android App in 2010, another for all trans through IOS in 2010, and so on...

Regards,

Anujay

[MarqueeCrew · Accepted solution]

@anujaymathur,

Consider creating a new partion field [Part] that includes the YEAR and the OS.  This field can be used as a PREFIX or a SUFFIX to your Dataset name.  You would then INPUT your 80GB data and OUTPUT the data using the "Take File/Table Name From Field" option at the bottom of the Output Data tool configuration options.

You can un-check the "Keep Field in Output" if you want to remove the field from your data.  This single output tool will "Filter" records to the appropriate partion with a SINGLE tool.

Cheers,

Mark

[anujaymathur]

Perfect! Thanks a lot for the prompt response.

Regards,

Anujay

[AshishBhavnani]

Hi Mark,

I followed your suggestions on this problem and found that the changes that you recommended to the "Output" tool produced a single sheet with multiple tabs corresponding to the unique values in the field used to partition the data.

Is there a way to output the data based on the unique values in the filed to altogether different files instead of different tabs within the same file.

For eg., if the field being used for this operation has 3 unique values say - Jan, Feb and Mar, then creating 3 different files with Jan_FileName, Feb_FileName and Mar_FileName instead of one file with Name -"FileName" and 3 different tabs - Jan,Feb and Mar.

Best Regards,

Ashish Bhavnani

[MarqueeCrew]

Please check out the workflow to see it in action.  All paths are relative.  You might want to specify where to save the files.

Cheers,
Mark

[AshishBhavnani]

Hi Mark,

Thanks for your prompt response.

I followed the workflow that you shared. I am again seeing a tab labeled as the Output FileName variable. The excel sheet that contains the tab still continues to retain the Name that was defined as part of the configuration of the Output Tool.

Could you point to anything that I might be doing incorrectly.

Best Regards,

Ashish

[MarqueeCrew]

Please PM me with your email and  availability either shortly or tomorrow morning (Eastern Time) and I'll setup a WebEx to review your workflow with you.

Thanks,
Mark

[MarqueeCrew]

We just had to modify the FULL Path and everything worked.  

Cheers,

Mark

[macquil]

hi, may I know what modification on the FULL path means? 

[Agarwal_Yash91]

Hi,

i am trying to achieve something like this but i need to split the data for transformation rather than writing the data in excel.

Regards,

Yash