topic Re: Syntax Error in Expression in QlikView

Syntax Error in Expression

Anonymous — Mon, 18 Aug 2014 11:44:45 GMT

Hello All,

LOAD application_no,

paycode as %PAYCODE_Emp,

request_id,

// request_date,

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,

userremarks,

// Stage1_approved,

// Stage1_approval_id,

// Stage1_approval_date,

// Stage1_approval_Remarks,

// Stage2_approved,

// Stage2_approval_id,

// Stage2_approval_date,

// Stage2_Approval_Remarks,

// hod_remarks,

chklbl1

// voucherno

// Stage3_approved,

// Stage3_approval_id,

// Stage3_approval_date,

// Stage3_approval_Remarks,

// LWPbal,

// IsArrear,

// BVRLeave

FROM

$(Vrawqvdspath)\LeaveRequest.qvd

(qvd);

Temp:

load

LeaveMonth,

if(LEAVECODE='CL', sum(leavedays)) as NoOfCLDays

Resident LeaveLedger Group by LeaveMonth;

Can somebody tell me why their is an error in this resident. It is showing "invalid expression error".

Regards,

Balraj

Re: Syntax Error in Expression

MK_QSL — Mon, 18 Aug 2014 11:48:18 GMT

Try

Temp:

load

LeaveMonth,

if(LEAVECODE='CL', sum(leavedays)) as NoOfCLDays

Resident LeaveLedger

Group by LeaveMonth, LEAVECODE;

Re: Syntax Error in Expression

tresB — Mon, 18 Aug 2014 11:54:50 GMT

Or, probably you need Sum(If.., rather than If(Sum(.... Try like:

Temp:

load

LeaveMonth,

Sum(if(LEAVECODE='CL', leavedays)) as NoOfCLDays

Resident LeaveLedger

Group by LeaveMonth;

Re: Syntax Error in Expression

its_anandrjs — Mon, 18 Aug 2014 11:55:47 GMT

Try your Temp table with different ways

Temp:

load

LeaveMonth,

Sum( if(LEAVECODE='CL', leavedays) ) as NoOfCLDays //If inside the SUM aggregation function.

Resident LeaveLedger Group by LeaveMonth;

Re: Syntax Error in Expression

Mon, 18 Aug 2014 11:57:25 GMT

try this place table name as LeaveLedger and then you can use table name in resident lode and also check if condition wrong if(LEAVECODE='CL', sum(leavedays),else part you need to place.

check code below

LeaveLedger:

LOAD application_no,

paycode as %PAYCODE_Emp,

request_id,

// request_date,

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,

userremarks,

// Stage1_approved,

// Stage1_approval_id,

// Stage1_approval_date,

// Stage1_approval_Remarks,

// Stage2_approved,

// Stage2_approval_id,

// Stage2_approval_date,

// Stage2_Approval_Remarks,

// hod_remarks,

chklbl1

// voucherno

// Stage3_approved,

// Stage3_approval_id,

// Stage3_approval_date,

// Stage3_approval_Remarks,

// LWPbal,

// IsArrear,

// BVRLeave

FROM

$(Vrawqvdspath)\LeaveRequest.qvd

(qvd);

Temp:

load

LeaveMonth,

if(LEAVECODE='CL', sum(leavedays),0) as NoOfCLDays

Resident LeaveLedger

Group by LeaveMonth;

Re: Syntax Error in Expression

Anonymous — Mon, 18 Aug 2014 11:58:42 GMT

Manish,

I am working on this KPI, Could you help me to get it?

LeaveLedger:

LOAD PAYCODE as %PAYCODE_Emp,

L01_ADD as Total_CL(predefined total leaves),

L02_ADD as Total_SL,

L03_ADD as Total_EL

FROM

$(Vrawqvdspath)\LeaveLedger.qvd

(qvd);

left join

LOAD application_no,

paycode as %PAYCODE_Emp,

request_id,

// request_date,

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(holds interger values)

FROM

$(Vrawqvdspath)\LeaveRequest.qvd

(qvd);

Need To Achieve:

@Emp_Name	LeaveMonth	LeaveDay	LEAVECODE	Accumulated Casual Leave	Accumulated Earned Leave	Accumulated Sick Leave	CL Accrued	EL Accrued	SLAccrued	Closing CL Balance	Closing EL Balance	Closing SL Balance
XXX	May	9	SL	10.5	10	7	0	0	2	10.5	10	5
XXX	Jun	18	SL	10.5	10	5	0	0	1	10.5	10	4
XXX	Jul	1	CL	10.5	10	4	1	0	0	9.5	10	4
XXX	Jul	10	OD	9.5	10	4	0	0	0	9.5	10	4
XXX	Jul	11	CL	9.5	10	4	1	0	0	8.5	10	4
XXX	Jul	12	OD	8.5	10	4	0	0	0	8.5	10	4
XXX	Aug	25	COF	8.5	10	4	0	0	0	8.5	10	4
XXX	Aug	26	COF	8.5	10	4	0	0	0	8.5	10	4

Let me know if you need any other info?

Regards,

Balraj