Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I think my problem is simple, but I couldn't solve it.
On my load script, I have a memory table "OP_Cost" like this:
OP_Cost:
Load
yearOP,
monthOP,
Dept,
Cost; // from ....
Ok, now I have the table "Performance" that is not associated with "OP_Cost":
Performance:
Load
yearP,
monthP,
Sum( {$<Dept={'Finance'}, yearOP={yearP}, monthOP={monthP}>} OP_Cost.Cost),
Target; // from ....
My doubt is how can I write the expression (in red), so I can get a sum for field "Cost", filtering the Dept, yearOP and monthOP.
Not necessary must be an expression. If not, what else can I do ??
Thanks all,
Rogério Faria
Well, thanks for all the replies. All of them helped me somehow.
But, after some tests, if decided to do it like this:
OP_Cost:
Load
yearOP,
monthOP,
Dept,
Cost,
0 as Target; // from ....
Concatenate(OP_Cost)
Load
yearP as YearOP,
monthP as monthOP,
Dept,
0 as Cost,
Target; // from ....
Like this, I only insert rows in the table, and the loading process finishes quickly.
Hi, Rogério. I don't think it is possible to do something like this.
You will have to create a temporary table and use left joint to get apply the values you want.
I think that, in your case, you would have something like this:
//AFTER OP_COST
Performance:
Load yearP, monthP, Target
from ...
tempPerformance:
Load yearOp as YearP, monthOP as monthP, sum(Cost)
resident OP_Cost
where Dept = 'Finance'
group by yearOp, monthOp;
left join (Performance)
load * resident tempPerformance;
drop table tempPerformance;
You probably need to correct something in my code before using it...
It's just to give an idea!
Hope it helps
Erich
EDIT: Sorry... just corrected the name!
Thanks Erich, by the way, its Rogério! (no prob)
The problem is that the table is very big, and Im trying to avoid future performance problems.
And I think that with expressions, the document load will be more optimized
If nobody else has another idea, I think i'll to use this way.
Rogério Faria
If you can, aggregate the target performance in the database (or data warehouse) prior to loading.
Hi, Rogério.
You can try to remove on step:
//AFTER OP_COST
Performance:
Load yearP, monthP, Target
from ...
left join (Performance)
Load yearOp as YearP, monthOP as monthP, sum(Cost)
resident OP_Cost
where Dept = 'Finance'
group by yearOp, monthOp;
I'd like to know alternatives as well.
Regards,
Erich
Try this
Performance:
Load
yearP,
monthP,
Target;
OP_Cost:
load
yearop,monthop,,sum(cost)
from ..
where exists(yearP,yearop)
and exists(monthP,monthop)
and Dept='Finanace'
group by yearop,monthop;
you can rename this table to someother name i just did this in the original table
Well, thanks for all the replies. All of them helped me somehow.
But, after some tests, if decided to do it like this:
OP_Cost:
Load
yearOP,
monthOP,
Dept,
Cost,
0 as Target; // from ....
Concatenate(OP_Cost)
Load
yearP as YearOP,
monthP as monthOP,
Dept,
0 as Cost,
Target; // from ....
Like this, I only insert rows in the table, and the loading process finishes quickly.