topic Re: Need Payroll Help In Qlikview in QlikView

Need Payroll Help In Qlikview

Tue, 04 Nov 2014 05:58:35 GMT

Dear All

I want a Payroll Help.

OT calculate OutTime-InTime-Onduty.

Onduty-Working Hour= OT

OT*OTRate= Amount.

Condition:

1) If InTime before 8:00AM OT calculate- Before InTime Hour

Example

InTime OutTime Onduty Working Hour OT OTRate Allow O.T Amount

7:35 AM 7:25PM 11:50 8:00 3.25 77 1 250

7:35 AM

7:25PM

11:50

8:00

3.25

0 Null

Re: Need Payroll Help In Qlikview

Tue, 04 Nov 2014 16:25:16 GMT

Dear all please help

See the attatchment

Re: Need Payroll Help In Qlikview

djsampat — Fri, 07 Nov 2014 07:11:02 GMT

Hi,

Try this

Data:

LOAD *,

interval(if(Check, rangemax(0,Time-3/8),0)) as OverTime,

interval(rangemin(3/8,Time)) as RegularTime;

LOAD *, interval(frac(OutTime) - frac(InTime)) as Time;

LOAD

FROM

[OvertimeCalc.xlsx]

(ooxml, embedded labels, table is TB01);

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

Regards

Dhruv

Re: Need Payroll Help In Qlikview

Fri, 07 Nov 2014 07:28:33 GMT

Hi Ala,

If I understand you correctly, please try my script below

LOAD

if([Check] = 1,(interval(interval#(OverTime,'hh:mm'),'hh:mm') * [OtRate]) * 24,'No OT') as OTAmount

;

LOAD

Interval([OnDuty] - [WorkingHrs],'hh:mm') as [OverTime]

;

LOAD

Interval([Out] - [In],'hh:mm') as [OnDuty]

;

LOAD

Check,

time('09:00:00','hh:mm' ) as WorkingHrs,

time(In,'hh:mm' ) as In,

time(Out,'hh:mm' ) as Out,

OtRate

;

LOAD * INLINE

[Check,In,Out,OtRate

1,05:30:00,20:00:00,5

0,05:30:00,18:00:00,8

1,09:00:00,19:00:00,8

];

-Charles