divide a column in script based on proportionality of other column

[andreip21]

Hi,

i have 2 separate tables (order id + p id + Sales and order id + p id + marketing cost)

how can i split the last column Sales into 2 parts (direct proportional) based on marketing cost (table 2)?

and the result to be (in my example) 25 and 75.

i want to try this in load script. thank you!

order id	p id	Sales
1	10	100

order id	p id	marketing cost	Sales
1	10	20	25
1	10	60	75

[petter · Accepted solution]

Like this:

COSTS:
LOAD * INLINE [
order id,p id,marketing cost
1,10,20
1,10,60
];

ORDER: LOAD * INLINE [
order id,p id,Sales
1,10,100
];

LEFT JOIN (ORDER)
LOAD
  [order id],
  [p id],
  Sum([marketing cost]) AS sum_marketing_cost
RESIDENT
  COSTS
GROUP BY
  [order id],[p id];
LEFT JOIN 
LOAD * RESIDENT COSTS;

DROP TABLE COSTS;

RESULT:
LOAD
  [order id],
  [p id],
  [marketing cost],
  [marketing cost]/sum_marketing_cost*Sales AS Sales
RESIDENT 
  ORDER;

DROP TABLE ORDER;

[andreip21]

hi, good enough, but i need one more thing.

for some order id x product id, i do not have marketing cost.

in the left join i am losing the Sales for these type of orders. how can i keep them?

Best,

Andrei

[petter]

Just add an extra line to keep the Sales in the last load statement:

 LOAD
     ......
     Sales AS OriginalSales,
    ......
;