topic Re: Sytntax Error in QlikView

Sytntax Error

Anonymous — Wed, 20 Aug 2014 06:12:28 GMT

Hello,

LOAD application_no,

paycode as %PAYCODE,

request_id,

// request_date,

date(leave_from,'DD-MMM-YYYY') as leave_from,

year(leave_from) as LeaveYear,

month(leave_from) as LeaveMonth,

day(leave_from) as LeaveDay,

leave_to,

//leave_to,

leavecode as LEAVECODE,

// halfday,

leavedays,

//if(leavecode='CL', sum(leavedays),'0') as CLAccrued,

Sum(if(leavecode='CL', leavedays)),

userremarks

FROM

$(Vrawqvdspath)\LeaveRequest.qvd

(qvd) Group by paycode, month(leave_from);

Can somebody tell me what is the error in this code?

It is giving me error.

Regards,

Balraj

Re: Sytntax Error

jonathandienst — Wed, 20 Aug 2014 06:17:48 GMT

When using grouping, every field not in an aggregation function (Sum(if...) in your case) must be listed in the group by clause

HTH

Jonathan

Re: Sytntax Error

Anonymous — Wed, 20 Aug 2014 06:22:17 GMT

Hello,

You meant to say, leavecode and leavedays should be listed in grouping?

Re: Sytntax Error

mjayachandran — Wed, 20 Aug 2014 06:26:12 GMT

Try this"

LOAD application_no,

paycode as %PAYCODE,

request_id,

// request_date,

date(leave_from,'DD-MMM-YYYY') as leave_from,

year(leave_from) as LeaveYear,

month(leave_from) as LeaveMonth,

day(leave_from) as LeaveDay,

leave_to,

//leave_to,

leavecode as LEAVECODE,

// halfday,

leavedays,

//if(leavecode='CL', sum(leavedays),'0') as CLAccrued,

Sum(if(leavecode='CL', leavedays)),

userremarks

FROM

$(Vrawqvdspath)\LeaveRequest.qvd

(qvd)

Group by paycode,

month(leave_from),

application_no,

request_id,

date(leave_from,'DD-MMM-YYYY'),

year(leave_from),

day(leave_from),

leave_to,

leavecode

leavedays,

userremarks ;

So, In Groupby you should have all the field which you are not aggregrating

Re: Sytntax Error

its_anandrjs — Wed, 20 Aug 2014 06:28:25 GMT

Hi,

When using Group by clause in the load you have to introduce all the fields available in the table see example otherwise use resident table load statement.

LOAD

application_no,

paycode as %PAYCODE,

request_id,

// request_date,

date(leave_from,'DD-MMM-YYYY') as leave_from,

year(leave_from) as LeaveYear,

month(leave_from) as LeaveMonth,

day(leave_from) as LeaveDay,

leave_to,

//leave_to,

leavecode as LEAVECODE,

// halfday,

leavedays,

//if(leavecode='CL', sum(leavedays),'0') as CLAccrued,

Sum(if(leavecode='CL', leavedays)),

userremarks

FROM

$(Vrawqvdspath)\LeaveRequest.qvd

(qvd) Group by

application_no,

paycode,

request_id,

date(leave_from,'DD-MMM-YYYY'),

year(leave_from),

month(leave_from),

day(leave_from),

leave_to,

leavecode,

leavedays,

userremarks;

Re: Sytntax Error

Wed, 20 Aug 2014 06:29:29 GMT

What columns (other than aggregation function) you are selecting in Load statement must be present in group by Clause.

Re: Sytntax Error

Wed, 20 Aug 2014 06:30:16 GMT

Apart from the above, one more cent is

date(leave_from,'DD-MMM-YYYY') as leave_from, -- is wrong

it should be

date#(leave_from,'DD-MMM-YYYY') as leave_from,

date#() is date formatting function. Please refer to Reference Manual

I guess you wanted to format the "leave_from" field in your load.

Thanks

Sri

Re: Sytntax Error

Anonymous — Wed, 20 Aug 2014 06:33:01 GMT

Anand,

Should I include leavecode and leavedays as well, as you mentioned?

Re: Sytntax Error

Anonymous — Wed, 20 Aug 2014 06:39:36 GMT

Thanks Anand, Its done.

Re: Sytntax Error

Anonymous — Wed, 20 Aug 2014 06:40:00 GMT

Thanks Mahesh, Its done.

Re: Sytntax Error

its_anandrjs — Wed, 20 Aug 2014 06:41:33 GMT

It should be or not necessary but depends on the requirement or the data also because when you use leavecode and leavedays in group by the data is aggregated according to the leavecode and leavedays also use some naming convention for the SUM statement like

Sum(if(leavecode='CL', leavedays)) as SumLeavdays,