Syntax of Expression

[Anonymous]

Hello All,

I am having following table:

Temp:

load

    PAYCODE,

    L01_ADD as Total_CL,

    L02_ADD as Total_SL,

    L03_ADD as Total_EL,

    application_no as App_No,

    request_id as Request_ID,

    leave_from as Leave_From,

    leave_to as Leave_To,

    leavecode as LeaveCode,

    leavedays as NoOfLeaveDays

    //if(leavecode='CL', sum(leavedays),'0') as [CL Accrued]

    Resident LeaveLedger;

Now, I need to do some expression calculation at back end, I am trying with some expression but it gives me error.

I need to calculate following fields at back end in this table:

Accumulated Casual Leave= sum(Total_CL)

CL Accrued=if(leavecode='CL', sum(leavedays),'0')

Closing CL Balance= Sum(Total_CL)-if(leavecode='CL', sum(leavedays),'0')

Please suggest, if possible at backend.

Regards,

Balraj

[#2]

Hi, You are missing group by clause in your Load statements that's why you are getting the error. You need to group and possible load the data in an aggregated table. Hope this solves otherwise please paste some sample data. -Ram

[Gysbert_Wassenaar]

You probably need to use the rangesum and peek functions and make sure your data is sorted correctly. Something like:

Temp:

load *, rangesum([Accumulated Casual Leave], -[CL Accrued]) as Closing CL Balance;

load

    PAYCODE,

    L01_ADD as Total_CL,

    L02_ADD as Total_SL,

    L03_ADD as Total_EL,

    application_no as App_No,

    request_id as Request_ID,

    leave_from as Leave_From,

    leave_to as Leave_To,

    leavecode as LeaveCode,

    leavedays as NoOfLeaveDays

    if(leavecode='CL' and PAYCODE=previous(PAYCODE) , rangesum(peek('CL Accrued'),leavedays),0) as [CL Accrued],

     if( PAYCODE=previous(PAYCODE), rangesum(peek('Accumulated Casual Leave'),L01_ADD),L01_ADD) as [Accumulated Casual Leave]

    Resident LeaveLedger

     Order By PAYCODE

;

[Anonymous]

Already tried group by but still giving me error.

[#5]

Post a sample file-Ram

[Anonymous]

attached.

[Anonymous]

Gysbert,

Still giving error, I have attached the QVD in the discussion. See, If you can help to sort it out..

Also, as per yesterday's conversation, I have tried, Above() and their siblings but not getting right info at top level, getting some garbage values.

Regards,

Balraj

[#8]

Can you try like this

TempLeave:

LOAD PAYCODE,

     Total_CL,

     Total_SL,

     Total_EL,

     App_No,

     Leave_From,

     Leave_To,

     LeaveCode,

     NoOfLeaveDays

FROM

Temp.qvd

(qvd);

Aggregated:

LOAD App_No,

if(LeaveCode = 'CL', sum(Total_CL)) as ACL,

if(LeaveCode = 'CL', sum(NoOfLeaveDays)) as CLA,

if(LeaveCode = 'CL', sum(Total_CL)) - if(LeaveCode = 'CL', sum(NoOfLeaveDays)) as CB

Resident TempLeave

group by App_No, LeaveCode;

drop table TempLeave;

[Anonymous]

It is giving wrong information, Total_CL are static for a person but here you are grouping which will group the Total_CL of all employee where QV will find leavecode='CL';

Would not work.

[Anonymous]

I have to generate this report with these information.

See, if you can help?

Regards,

Balraj