Support Questions

Find answers, ask questions, and share your expertise

Sales Data Split by Sales Rep?

Explorer

Our sales team are extracting the months sales opportunities, actuals etc. Within the one CSV file there are potentially 100+ rows. Now, we need to create new flow files (array?) containing individual sales people. We have 20+ sales people each working on many opportunities.

salesOpId,salesRepId, salesOpStage, .....

142435,135235,inception ....

142436,572856,inception ....

142437,135235,pipelining ....

142435,572856,inception....

149468,135235,contract....

149464,135653,contract....

 

As you can see

salesRepId 135235 is working against 3 opportunities, (142436,142437,149468)

salesRepId 572856 is working against 2 opportunities (142436, 142435)

salesRepId 135653 is working on a single opportunity (149464)

 

For this example I need 3 FlowFiles created (which will be sent to their respective managers); I'm capable of reading the CSV changing it to Json; but now I'm completely stumped on the next step - split the json?

 

Any help would be greatly appreciated. 

Ron M.

 

 

1 ACCEPTED SOLUTION

Master Guru

@RonMilne 

 

I recommend taking your initial CSV record file and partitioning by the "salesRepId" in to multiple new JSON records.  This can be accomplished using the PartitionRecord processor utilizing a CSVReader and a JsonRecordSetWriter.  

Your PartitionRecord processor configuration would look like this:

MattWho_0-1643145705413.png

 

Your CSVReader would be configured something like this (you'll need to modify it for your specific record's Schema:

MattWho_1-1643145807713.png

Note: Pop-out shows the "Schema Text" property and don't forget to set "Treat First Line as Header" property to "true"

The JsonRecordSetWriter would need to be configured to produce the desired JSON record output format.  However, just leaving default configuration will out put a separate FlowFile for each unique "SalesRepId".

 

If you found this response assisted with your query, please take a moment to login and click on "Accept as Solution" below this post.

Thank you,

Matt

 

View solution in original post

1 REPLY 1

Master Guru

@RonMilne 

 

I recommend taking your initial CSV record file and partitioning by the "salesRepId" in to multiple new JSON records.  This can be accomplished using the PartitionRecord processor utilizing a CSVReader and a JsonRecordSetWriter.  

Your PartitionRecord processor configuration would look like this:

MattWho_0-1643145705413.png

 

Your CSVReader would be configured something like this (you'll need to modify it for your specific record's Schema:

MattWho_1-1643145807713.png

Note: Pop-out shows the "Schema Text" property and don't forget to set "Treat First Line as Header" property to "true"

The JsonRecordSetWriter would need to be configured to produce the desired JSON record output format.  However, just leaving default configuration will out put a separate FlowFile for each unique "SalesRepId".

 

If you found this response assisted with your query, please take a moment to login and click on "Accept as Solution" below this post.

Thank you,

Matt

 

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.