Need help with Transform Map scripting

[brown9394]

On a Transform map, how would I go about doing a groupBy source.field_1 in order to aggregate source.field_2 count (SUM)?

I have a transform map that maps to multiple tables, One of the table is 'u_customer' and I need to set the target.u_total_quantity field to a SUM of source.u_quantity field. This is based on doing a groupBy source.u_type field, and then adding the Quantity field and setting the target field. Current mapping of source data to 'u_customer' table does not change, however, before transform is done, I need a way to query through the source data, and set the target.u_total_quantity based on consolidating source Type and aggregating source Quantity.

For example: This is a sample of what the data looks like --

Customer ID	Type	Quantity
12345	Standard	20
12345	Standard	20
12345	Standard	20
12345	Premium	10
12345	Premium	10
12345	Enhanced	30
12345	Enhanced	30

• Current Coalesce field is u_customer_id (Customer ID)
• u_type (Type) does NOT map to u_customer (Customer) table.
• However, I need to do a groupBy Type to end up with only 1 set of Quantity for each Type because Data is repeated due to being on the same row as the Order data, where Order numbers could be different, and maps to a different table, but customer details do not change.
• The only thing we're concerned with with is mapping a Total Quantity to the target table. So based on the example data above, expected Quantity for each type would be:
  • Standard - 20
  • Premium - 10
  • Enhanced - 30

• Next, SUM consolidated quantity by source.u_type and set the target.u_total_quantity - 60

Also, This data is a running total, so the target.u_total_quantity will always be over written with a new total if there's a change in source.u_quantity by Type.

I'm thinking I need to do an After transform script, but not sure how even begin with that. I would greatly appreciate any help! 

[Tony Chatfield1]

This post is a duplicate of the post you made yesterday here.

Re: Transform map aggregate Import data source fie... - ServiceNow Community

It is not necessary to post the same basic question\issue multiple times

and the most appropriate action would be for you to read and respond to support\solution provided in the original thread.

[brown9394]

Ops, my apologies! I didn't mean to post a new question, I thought i modified the existing one, or was trying to so it was more clear, however, now it makes sense why your replies disappeared. 

With that said, your solution is not quite what I'm looking for. I know how I could script it so I can set the source.field to target.field. However, that's not exactly what I'm asking. 

If you read the details carefully, taking the sample data, I need help doing a groupBy source.u_type, which then I would end up with ONLY 3 records and then SUM the Quantities, ending with a Total Quantity of 60.

[brown9394 · Accepted solution]

I got it working by an onStart script and setting a global object to aggregate the import data and calling it from the field map script. 

[Saloni Suthar]

Would you be able to post your script?