Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table Table1 with 3 rows (A,B,Costs):
Table1 | |
Settore | PL |
A | 30 |
B | 20 |
Costs | (10) |
I have to produce a new table Table2 like this:
Table2 | |
Settore | PL |
A | 22 |
B | 18 |
where
Table2.A = Table1.A + 80% Table1.Costs
Table2.B = Table1B + 20% Table1.Costs
How can I create the new Table2?
Thanks!
Savio
Try this
Table:
LOAD *,
Pick(Match(Settore, 'A', 'B'), 0.80, 0.20) as PercAlloc;
LOAD * INLINE [
Settore, PL
A, 30
B, 20
Costs, -10
];
Left Join (Table)
LOAD PL as PL_New
Resident Table
Where Settore = 'Costs';
FinalTable:
LOAD Settore,
(PL + (PL_New*PercAlloc)) as PL
Resident Table
Where Settore <> 'Costs';
DROP Table Table;
front end? back end? how is the cost %age determined?
I would like insert it in a script in the data load editor.
I need to create the table 2 adding costs. I have the total costs in the row in the table 1.
In the table 2 I need to add cost The total costs (-10) needs to be added 80% to Settore A and 20% to Settore B.
The PL field of the table 2 is for :
PL Settore A = 30 + (80% * (- 10))
PL Settore B = 20 + (20%* (-10))
Thanks!
But where are the 80% and 20% number coming from? They are always 80% and 20%? What if there is a C? What would be the percentage? or there is always A and B?
I have to apply costs only when Settore = A or Settore = B. If Settore = C or other I haven't costs.
The percentage 80% and 20% are constants for Settore = A (80%) and Settore = B (20%) I will specify them in the script or insert in a table.
I have never added or reduced rows in a table. Maybe I need to understand how I can add o reduce rows and calculate new values for the output table.
Can you help me?
Thanks!
Try this
Table:
LOAD *,
Pick(Match(Settore, 'A', 'B'), 0.80, 0.20) as PercAlloc;
LOAD * INLINE [
Settore, PL
A, 30
B, 20
Costs, -10
];
Left Join (Table)
LOAD PL as PL_New
Resident Table
Where Settore = 'Costs';
FinalTable:
LOAD Settore,
(PL + (PL_New*PercAlloc)) as PL
Resident Table
Where Settore <> 'Costs';
DROP Table Table;
Great! It works!
Thank you very much!