Need Payroll Help In Qlikview

[#1]

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

77

0               Null

[#2]

Dear all please help

See the attatchment

[djsampat · Accepted solution]

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

[#4]

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