not working in script

[sibideepak]

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

[varunjain]

Hi

Is it giving some error?

[tresesco]

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

[sibideepak]

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;

[MayilVahanan]

Hi

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?

[sundarakumar]

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

[#7]

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

Hope this helps

[sibideepak]

Hi MayilVahanan,

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

Yes , all the field are in the same table.

[MayilVahanan]

Hi

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;