Summing cost by product and merging it with existing data

[RogerG]

so I have a table of products that consist of supply cost, development cost and internal cost

Internal cost is calculated based on supply cost - development costs

I am trying to do all this in the load script in order to keep the interface charts clean.  I am afraid that may not be possible

My data set looks like this

Products:

Load * inline [

Product, account, year, cost

Product1, Supply, 2020, 1000

Product1, Development,2020 2000

Product1, internals, 2020, // need to calculate this value

Product1, Supply, 2021, 3000

Product1, Development, 2021, 5000

Product1, Internal, 2021, // need to calculate this value

]

so the calculation would be development - supply = 1000

[PriyankaShivhare]

May be not the best solution but can be done like below

test:

load * ,
Product&'-'&year as %Key

Inline [

Product, account, year, cost

Product1, Supply, 2020, 1000

Product1, Development,2020, 2000

Product1, internals, 2020,

Product1, Supply, 2021, 3000

Product1, Development, 2021, 5000

Product1, internals, 2021,

];

temp:
Load  %Key,
sum(cost) as DevelopmentCost
Resident test
where account='Development'
group by %Key ;

left join(temp)

Load %Key,
sum(cost) as SupplyCost
Resident test
where account='Supply'
group by %Key ;

MapInternalCost:
Mapping Load
%Key,
DevelopmentCost-SupplyCost as InternalCost
Resident temp;

drop table temp;

NoConcatenate
Test1:
Load *,
if(account='internals',Applymap('MapInternalCost',%Key,null()),cost) as NewCost
Resident test;

drop table test;