Splitting Records dynamically, based on a column values

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

Best Practices

Dynamic Processing

Iterative Macro

Accepted answers

MarqueeCrew

@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

All comments

MarqueeCrew

@anujaymathur,

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

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

Quick Links

This months top contributors

MarqueeCrew 13708

mceleavey 5990

JosephSerpis 5883

patrick_digan 5758

SeanAdams 5708