topic Re: not working in script in QlikView

not working in script

sibideepak — Fri, 12 Sep 2014 08:01:44 GMT

Hi ,

the following statement is working fine with text box .

if(q1<max(start),sum(If(start<=q1,price)),sum(If(start<=q1,price))+((q1-max(start))*($(vNew))))

but in script loader I can't do this ..

help me to do the same in script loader..

Thanks,

Deepak sibi

Re: not working in script

varunjain — Fri, 12 Sep 2014 08:04:57 GMT

Is it giving some error?

Re: not working in script

tresB — Fri, 12 Sep 2014 08:05:09 GMT

If you use aggregation function(max,sum,....) in script, you have to use Group By clause too. Perhaps you are missing that.

Re: not working in script

sibideepak — Fri, 12 Sep 2014 08:08:13 GMT

Error : Nested aggregation not allowed

my script

Final:

Load prod,max(if(q1<max(start),sum(If(start<=q1,price)),sum(If(start<=q1,price))+((q1-$(vMax))*($(vNew))))) as sum1,sum(If(start>q1 And start <=q1+q2,price)) as sum2,sum(If(start>q1+q2 And start <=q1+q2+q3,price)) as sum3,

sum(If(start>q1+q2+q3 And start <=q1+q2+q3+q4,price)) as sum4 Resident target Group by prod;

Re: not working in script

MayilVahanan — Fri, 12 Sep 2014 08:09:12 GMT

May be

if(q1<max(start),sum(If(start<=q1,price)),sum(If(start<=q1,price))+((q1-max(start))*($(vNew))))

1. Missing the group by clause

2. Check the "vNew" variable

3. All the fields are in same table?

Re: not working in script

sundarakumar — Fri, 12 Sep 2014 08:09:53 GMT

u cannot use aggregate functions in script laoder(sum, max,min) unless u r using a group be or it is a single column table.

what i would suggest is first find the max(start) in the back end like

one:

load max(start) as max_start

from table;

then load it to a variable

vMaxStart=peek('One',0,max_start) ;

then use the variable while loading the table that has price value.

-Sundar

Re: not working in script

Fri, 12 Sep 2014 08:14:36 GMT

Try using the function lastvalue() and order by in ur load script.

Hope this helps

Re: not working in script

sibideepak — Fri, 12 Sep 2014 09:22:17 GMT

Hi MayilVahanan,

vNew=sum({<start={$(=max(start))}>}price)

Yes , all the field are in the same table.

Re: not working in script

MayilVahanan — Fri, 12 Sep 2014 09:46:36 GMT

You need to include the fields in "group by" except whose fields in aggregation function.. Instead of using nested aggregation, use preceding load with max function like sample.

for ex:

Load * Inline

[

customid, order, status

C100, 10, yes

C100, 20, yes

C101, 10, yes

C102, 50, no

C103, 100, yes

C104, 1000, no

];

Load customid,max(Sumorder) as maxorder group by customid;

Load customid, (if(status = 'yes',sum(order))) as Sumorder Resident Table1

Group by customid, status;