Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
nithya123
New Contributor II

how to calculate the hours based on emp id and date

Emp_idE_dateE_Hours
11/1/20013:00:00 AM
11/1/20013:00:00 AM
11/2/20016:00:00 AM
11/3/20014:00:00 AM
11/4/20017:00:00 AM
11/5/20018:00:00 AM
21/1/20019:00:00 AM
21/2/200110:00:00 AM
21/3/200111:00:00 AM

how to calculate the hours based on emp id and date

7 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: how to calculate the hours based on emp id and date

Is E_hours a time or a number of hours? What kind of information is stored in E_hours?

Perhaps sum(E_Hours) gives you the result you're looking for.


talk is cheap, supply exceeds demand
Highlighted
nithya123
New Contributor II

Re: how to calculate the hours based on emp id and date

e_hours contains hours

Highlighted
parimikittu
Contributor II

Re: how to calculate the hours based on emp id and date

We need more inputs to help you, Such as what does E_hours mean? is it a login time where to want to calculate the no of hours of the Emp based on Diff btw time? or is it just no of hr?

Highlighted
MVP & Luminary
MVP & Luminary

Re: how to calculate the hours based on emp id and date

Then sum(E_Hours) should give you the result you need. Perhaps you need to format it with the Interval function:

Interval(Sum(E_Hours),'hh:mm:ss')


talk is cheap, supply exceeds demand
Highlighted
nithya123
New Contributor II

Re: how to calculate the hours based on emp id and date

TABLE1:

LOAD Emp_id,

     E_date,

     E_Hours,

     Billed_status

FROM

[..\Rm poc_Qlikview\Emp_count.xlsx]

(ooxml, embedded labels, table is Sheet7);

table2:

load Emp_id,

     E_date, sum(E_Hours) as Worked_hours Resident TABLE1 Group by Emp_id,E_date;

    

table3:

LOAD Emp_id,

     E_date,if(Worked_hours<4,'Available',

if(Worked_hours <8 and Worked_hours>=4,'Partially Occupied',

if(Worked_hours>=8,'Fully Occupied'))) as Allocation resident table2;

i got the answer but now i want to Billed_status = 'yes' which employee have to display,

but here billed_status containd table 1 and worked hours contains table 2 both are conflict . how to overcome and take worked_hours based on billed status?

Highlighted
qlikdeez
Valued Contributor

Re: how to calculate the hours based on emp id and date

Maybe try

table2:

load Emp_id,

     E_date, sum(E_Hours) as Worked_hours Resident TABLE1

where match(Billed_status, 'yes')

Group by Emp_id,E_date;

on step 2

Highlighted
nithya123
New Contributor II

Re: how to calculate the hours based on emp id and date

its just a no.of hour only