topic Re: SUM(IF) giving an error at load script in QlikView

SUM(IF) giving an error at load script

Tue, 20 May 2014 07:04:34 GMT

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 ?

Re: SUM(IF) giving an error at load script

alexandros17 — Tue, 20 May 2014 07:08:06 GMT

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

Let me know

Re: SUM(IF) giving an error at load script

its_anandrjs — Tue, 20 May 2014 07:08:32 GMT

Write like below script or give the field name for

RowNo() as RowId

RowNo() as RowId

FROM

my file;

Re: SUM(IF) giving an error at load script

lironbaram — Tue, 20 May 2014 07:11:42 GMT

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

Re: SUM(IF) giving an error at load script

its_anandrjs — Tue, 20 May 2014 07:12:37 GMT

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;

Re: SUM(IF) giving an error at load script

Tue, 20 May 2014 07:12:52 GMT

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

Re: SUM(IF) giving an error at load script

Tue, 20 May 2014 07:15:34 GMT

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 ?

Re: SUM(IF) giving an error at load script

its_anandrjs — Tue, 20 May 2014 07:37:53 GMT

In expression you can try with

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

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