topic Re: Syntax of Expression in QlikView

Syntax of Expression

Anonymous — Tue, 19 Aug 2014 09:35:29 GMT

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

Re: Syntax of Expression

Tue, 19 Aug 2014 09:48:50 GMT

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

Re: Syntax of Expression

Gysbert_Wassenaar — Tue, 19 Aug 2014 09:57:29 GMT

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

;

Re: Syntax of Expression

Anonymous — Tue, 19 Aug 2014 10:02:12 GMT

Already tried group by but still giving me error.

Re: Syntax of Expression

Tue, 19 Aug 2014 10:03:58 GMT

Post a sample file-Ram

Re: Syntax of Expression

Anonymous — Tue, 19 Aug 2014 10:23:46 GMT

attached.

Re: Syntax of Expression

Anonymous — Tue, 19 Aug 2014 10:41:24 GMT

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

Re: Syntax of Expression

Tue, 19 Aug 2014 11:10:16 GMT

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;

Re: Syntax of Expression

Anonymous — Tue, 19 Aug 2014 11:18:28 GMT

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.

Re: Syntax of Expression

Anonymous — Tue, 19 Aug 2014 11:23:34 GMT

I have to generate this report with these information.

See, if you can help?

Regards,

Balraj

Re: Re: Syntax of Expression

Gysbert_Wassenaar — Tue, 19 Aug 2014 13:14:23 GMT

See attached qvw for calculation of the XX_Accrued fields.

I have no idea what Accumulated Casual Leave = sum(Total_CL) is supposed to be. For paycode 3001 the sum of Total_CL is 5782. Surely that cannot be a number of leave days?

Re: Re: Syntax of Expression

Anonymous — Wed, 20 Aug 2014 05:10:35 GMT

True, It is not number of leave days.

Total_CL is a static field which holds the predefined leave balance for a particular paycode.

Re: Syntax of Expression

Anonymous — Wed, 20 Aug 2014 05:18:39 GMT

Gysbert,

I am not able to download this file, showing "failed to open this file".

Re: Syntax of Expression

Gysbert_Wassenaar — Wed, 20 Aug 2014 09:46:58 GMT

Try again. Or try again using a different browser or from a different computer. I can download it without problems.