Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need Payroll Help In Qlikview

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    
1 Solution

Accepted Solutions
djsampat
Creator II
Creator II

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

View solution in original post

3 Replies
Not applicable
Author

Dear all please help

See the attatchment

djsampat
Creator II
Creator II

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

Not applicable
Author

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