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 your configuration\requirement is not clear, as you have stated that your coalesce behaviour is not changing, yet you now have 3 distinct combinations for each customer and want to sum the quantity - but are not identifying which 'type' is involved, or how you decide which type to evaluate/sum, and without having a record for each customer\type combination there is no way to know which type is to be summed.

If you coalesce on the customer id AND on type, then in a beforeTransform script you can simply sum the Quantity

IE target.quantityField = target.quantityField + source.quantityField;

If this solution does not meet your scenario the perhaps you could update this thread with some clear details, so that the forum can better understand you requirements?

[brown9394]

Hi Tony, thanks for your reply. Let me clarify a bit of details on the requirements. 

The source.u_type is not mapped to the target (u_customer) table. This data source maps to different tables for order related data. The Customer table only has the Total Quantity regardless of the Type. 

Furthermore, this data being mapped is an all time running total, meaning, If customer_id '12345' has Type == Standard, Quantity of 20, that will always remain and added onto for next time we import from the same source, where if there was an increase of 5 in the quantity, then it would read '25', and it is true for all duplicated entries. If 5 line items will show up customer_id '12345', Type 'Standard', quantity will be 25 for each. 

Which is why I need to group by Type to consolidate however many duplicated entries by 'type' into 1, and only take 25 as the quantity and add that to other duplicated Type entries for the same customer by consolidating those line items into 1 entry. We don't want to add Quantity of duplicated entries for each type, rather group by Type / Quantity and only add different Type quantity as One Total Quantity.  

I hope that made sense, if not please let me know and I can further fill in the gaps. 

Thanks again! 

[Tony Chatfield1]

if you are only interested in total quantity, then you should be able to update the target total for each row in the transform using beforeTransform script, but you may have to remove the coalesce check from the mapping and move this to the before script so that the row is processed regardless of the record existing or not.  
IE lookup your target with a glidequery, if the record exists update the total within the glidequery and set ignore = true, so that a new record is not created by the transform. Something like

var myAnswer = false;

var recCheck = new GlideRecord('yourTable');
recCheck.addQuery('coalesceField', source.coalesceValue);
recCheck.query();

//If we find an existing record we want to update the record and then set the transform to ignore
//So that a new\duplicate record is not inserted.
if(recCheck.next) {
recChec.totalField = recCheck.totalField + parseInt(source.quantityField);
//map other fields if required
recCheck.update(); 
myAnswer = true;
}

//no else required as your tranform map will generate a new record if myAnswer = false;

ignore = myAnswer;

Transformation script variables (servicenow.com)

[brown9394]

Hi Tony, 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.