Syntax Error in Expression

[Anonymous]

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

[MK_QSL · Accepted solution]

Try

Temp:

load

    LeaveMonth,

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

Resident LeaveLedger

Group by LeaveMonth, LEAVECODE;

[tresesco]

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;

[its_anandrjs]

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;

[#5]

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;

[Anonymous]

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