Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Expression on load script

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

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Expression on load script

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.

6 Replies
erich_shiino
Honored Contributor

Re: Expression on load script

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!

Not applicable

Expression on load script

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

Not applicable

Expression on load script

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

erich_shiino
Honored Contributor

Expression on load script

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
Valued Contributor

Expression on load script

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

Not applicable

Re: Expression on load script

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.

Community Browser