Transform map aggregate Import data source field to Target field.

[brown9394]

Hi experts, 

I have a Transform map where I'm doing a coalesce on the 'customer_id' to target table 'u_customer'. Order data is duplicated in the source table, and the way to ensure only 1 record entry gets inserted/updated is by Customer ID (customer_id). Recently, a new column has been introduced in the source data 'u_type' ('Type' == 'Standard', 'Enhanced', 'Premium') with 3 possible options. The coalescing by Customer ID doesn't change, however, I need to get a total of 'u_quantity' column based on 'u_type' column. 

For example, in the table below, I need to group by 'source.u_type' (Type) to get an aggregate total of 'source.u_quantity' (Quantity) and set that to 'target.u_total_quantity' (Total Quantity). So that Total Quantity == '60' in the customer record '12345' in the target Customer table (Target Table: 'u_customer').

20 (Standard) + 10 (Premium) + 30 (Enhanced) = 60

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

Any help would be greatly appreciated! 

Thank you in advanced 🙂 

[Tony Chatfield1]

Hi, unfortunately I am not able to understand your requirement regarding type or how grouping by type can have any impact on a quantity total. You have stated that type does not exist in the target table, yet then indicate that you need to group\count these values based on type and push these to the quantity field. Are you trying to create a numerical value based a unique type being included in the importSet and\or the highest quantity per type?

Perhaps you can review your comments and clarify how this needs to work end to end.
Can the quantity for a type be different per entry?

if I have

premium 5

premium 3

premium 2

standard 10

what are you expecting to add to your target quantity?

[brown9394]

Hi Tony, 

For any Customer, the Type will NOT have different quantity, which is why I need to consolidate by Type. Customer ID = 12345 (Unique)

If Premium type shows up 3 times on the data source, it will all have the same #Quantity.

Premium - 5

Premium - 5

Premium - 5

If Standard type shows up 5 times on the data source, it will all have the same #Quantity. 

Standard - 3

Standard - 3

Standard - 3

Standard - 3

Standard - 3

The Total Quantity for Customer ID = 12345 should be Premium 5 + Standard 3 = 8

[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.