SUM(IF) giving an error at load script

[#1]

Hello All,

I want to do the following Load:

LOAD DEVICEID,

     MODULEID,

     EVENTDATE,

     //Date(Date#([EVENTDATE],'YYYYMMDD'),'MM/DD/YYYY') as date,

     MODULESNDRM,

     WASHZONEID,

     POSITION1,

     MAXTEMPPOSITION1,

     TEMPDELTAPOSITION1,

   //sum(if(POSITION1>0),TEMPDELTAPOSITION1) as temp_1,

   



//sum(if(POSITION1 >0, TEMPDELTAPOSITION1)) as temp,



     POSITION2 ,

     MAXTEMPPOSITION2,

     TEMPDELTAPOSITION2,

     POSITION3 ,

     MAXTEMPPOSITION3,

     TEMPDELTAPOSITION3,

     FILEID,

     LOADDATE, RowNo()

FROM

my file;

but it is giving an error in expression , can somebody please help me here ?

[alexandros17]

If you use a Sum in your Load you have to use the group by clause

Let me know

[its_anandrjs]

Write like below script or give the field name for

RowNo() as RowId

LOAD DEVICEID,

     MODULEID,

     EVENTDATE,

     //Date(Date#([EVENTDATE],'YYYYMMDD'),'MM/DD/YYYY') as date,

     MODULESNDRM,

     WASHZONEID,

     POSITION1,

     MAXTEMPPOSITION1,

     TEMPDELTAPOSITION1,

   //sum(if(POSITION1>0),TEMPDELTAPOSITION1) as temp_1,

   



//sum(if(POSITION1 >0, TEMPDELTAPOSITION1)) as temp,



     POSITION2 ,

     MAXTEMPPOSITION2,

     TEMPDELTAPOSITION2,

     POSITION3 ,

     MAXTEMPPOSITION3,

     TEMPDELTAPOSITION3,

     FILEID,

     LOADDATE,

RowNo() as RowId

FROM

my file;

[lironbaram]

hi,

when you want to use aggregate function in load script

you need to use group by also

other wise you don't need the sum function in your script just the if part of the expression

[its_anandrjs]

Or if you use SUM in table yes you have to aggregate the table by using group by like

Eg:-

Load

ID,

Name

Sum(amount) as Amt

Resident TableSource

Group By

ID,

Name;

[#6]

I want to be able to sum All the TEMPDELTAPOSITION1 and divide by 1000 where POSITION1<0, I know I have to use group by clause but after having tried few group by combinations I am not getting the desired result

[#7]

I want to have Row number of the resultset

( {$<POSITION1 ={"=[POSITION1]>0"}>} TEMPDELTAPOSITION1/1000)

as my X-Axis, but I thought it is not possible to do that directly on the Line chart hence I thought I would do it in the load script itself, is it possible to do it in the chart directly ?

[its_anandrjs]

In expression you can try with

Sum ( {$<POSITION1 = {"$( =[POSITION1] > 0 )" }>} TEMPDELTAPOSITION1) / 1000

Or

Sum ( {<POSITION1 = {'$( =[POSITION1] > 0 )' }>} TEMPDELTAPOSITION1) / 1000