topic Expression on load script in QlikView

Expression on load script

Fri, 27 May 2011 18:53:08 GMT

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

Re: Expression on load script

erichshiino — Fri, 27 May 2011 23:52:40 GMT

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!

Expression on load script

Sat, 28 May 2011 02:09:37 GMT

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

Expression on load script

Sat, 28 May 2011 16:15:37 GMT

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

Expression on load script

erichshiino — Mon, 30 May 2011 05:18:51 GMT

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

Expression on load script

qliksus — Mon, 30 May 2011 06:08:38 GMT

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

Re: Expression on load script

Mon, 06 Jun 2011 18:04:33 GMT

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.