Scripting on multiple coalesce fields in transform map

[ruchi_jha]

Hello, 
I have  a transform map where thereare 4 fields as coalesce. When i import any file, records get inserted/updated in my target table based on coalesce field mapping functionality of transform map. I do not want the records to be directly updated when coalesce matches. Rather i need to update one field 'cost' and then insert/update in target table.

For example, my file has following sample records which will be inserted/updated in target table.

Model No (coalesce)	Brand (coalesce)	Year(coalesce)	Cost
100	Mercedes	2013	2Cr
100	Mercedes	2013	2.5Cr
100	Mercedes	2015	1.5 Cr
200	Audi	2015	3.0 Cr
200	Audi	2015	2.0 Cr
200	Audi	2016	1.0Cr

In ideal case scenario, when i upload above file, there are 4 records total inserted as below:

Model No (coalesce)	Brand (coalesce)	Year(coalesce)	Cost
100	Mercedes	2013	2.5Cr
100	Mercedes	2015	1.5 Cr
200	Audi	2015	2.0Cr
200	Audi	2016	1.0Cr

But I want the cost field to be added and updated when coalesce field is matched. It should like as below :

Model No (coalesce)	Brand (coalesce)	Year(coalesce)	Cost
100	Mercedes	2013	4.5 Cr
100	Mercedes	2015	1.5 Cr
200	Audi	2015	5.0 Cr
200	Audi	2016	1.0 Cr

I am trying an on before script but that's not working. Records are getting inserted as ideal case scenario.
Any leads on how to achieve this ?I am trying below onbefore script:

if(action == "update"){
		var cost = +target.u_total_cost+ +source.u_total_cost;
	}

target.u_total_cost=cost;

[Omkar Mone]

Hello,

You can try having an onBefore transform script to do this - 

function runTransformScript(source, target, map, log) {
var cost="";
    if (target.sys_id) {
         cost = target.u_total_cost + source.u_total_cost;
        target.salary = parseInt(cost);
    }
}

[ruchi_jha]

Hello Omkar,
In my log I am getting the total cost as expected but it's not updating in table.
I am trying this:

if (action == "update") {
        var cost = +target.u_total_cost + +source.u_total_cost;
        target.u_total_cost = cost;
    }
gs.log('cost'+target.u_total_cost);//getting expected value in this cost but in table it is updating the ideal case scenrio that is the most recent cost value because of coalesce

[Omkar Mone]

Can you check if any other logic overrides the field? Do you have any BR or any other logic written for this ?

[Ravi Gaurav]

Hey,

you can use an "onBefore" Transform Map script

// Only execute if the action is an update
if (action === 'update') {
// Check if the target already has a cost
if (target.u_total_cost) {
// Add the existing cost to the new cost from the source
var newCost = parseFloat(target.u_total_cost) + parseFloat(source.u_total_cost);
target.u_total_cost = newCost.toString(); // Convert back to string if needed
} else {
// If there's no existing cost, just set it to the source cost
target.u_total_cost = source.u_total_cost;
}
} else if (action === 'insert') {
// For new records, simply set the cost as is
target.u_total_cost = source.u_total_cost;
}

[ruchi_jha · Accepted solution]

This is fixed now. There was a minor error in my code.
This is my updated code:

if (action == "update") {
        var cost = +target.u_total_cost + +source.u_total_cost;
        source.u_total_cost = cost.toString(); //change done here
    }