Expression on load script

[#1]

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

[erichshiino]

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!

[#3]

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

[#4]

If you can, aggregate the target performance in the database (or data warehouse) prior to loading. 

[erichshiino]

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

[qliksus]

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

[#7 · Accepted solution]

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.