Crazy reuirement in qlik life for time calculation?

[Anonymous]

Hi Folks,

i have a crazy requirement

background of this requirement is

i am capturing the employee information on daily basis and each task wise irrespective of shifts

it means example

emp a shift starts at 8 am

emp b shift starts at 1 pm

emp c shift starts at 6 pm

capturing the information irrespective of login time

here for emp a hour 1 is 8 am to 9 am

for emp b  is 1pm to 2 pm

for emp c  is  6pm to 7 pm

so hour 1 can be anything( any time)  ,

now here my requiremnet is i want see the hour1,hour2....,hour9 used tome for all employees

it means   emp a will start shift at 8 am ,

08:00 am to 08:30 am   meeting

08:30 am to  08:50 am  away

08:50 am to 09:00 am is mailbox checking

like wise for all employess we have information

now i want to see all employee usage in hour 1,hour2....hour 9.

tricky thing is here:

if employee exactly ends hour 1 end time then we can easy calculate it but for some of the employees tasks ends like this

08:00 am to 08:30 am   meeting

08:30 am to  08:50 am  away

08:50 am to 09:10 am is mailbox checking

from abobe data mailbox task is falling under   hour 1 and hour 2

so here we have to take 10 mins of time from mailbox and put it into hour 2. and 10 mins time to hour 1 ,

mail box task should be there in both hour 1 and hour 2

I am attaching sample data file to the link pls have a look and let me know ur thoughts

i have source columns like this

Team    -team name (database)

Region    -(india)

SPOC     -(emp name)

Date     -date

Start Time -starting time of each employee

End Time - ending time of employee   

Time Spent (mins) - time spent

Broad Category  - used for (like mails,meetings,away...)

attached   my work application .....

[Anil_Babu_Samineni]

Can you read this? Buckets

[Anonymous]

Can u pls look into this , am not understanding

[Anil_Babu_Samineni]

Can we split as short question rather pages. Because, I am not en-count the issue where we start from. I meant say, please tell us expected result by providing the sample Excel

[arvind_patil]

Hi ,

Please tell us expected result by providing the sample Excel.

Thanks,

Arvind Patil

Crazy reuirement in qlik life for time calculation?

[Anil_Babu_Samineni]

May be this? I've seen in your excel from sheet2. I assume that is output you need

[Anonymous]

Yes Exactly Anil

[Anonymous]

I have attached xl file

pls see the sheet2 for out put

[Anil_Babu_Samineni]

Haha, May be you can close this thread by flag correct answer and Helpful

Note - We are always help you if you still trouble please free to ask till satisfied

[Anonymous]

no anil ,

hour 1 ,hour 2 columns are calculated in xl level

which i want to do this calculation in qlikview level

load

only these columns from  from xl file ...  like

Temp:

LOAD Team as Empname,

     Region,

     SPOC as empname ,

     Date,

     [Start Time] as Startrime,

     [End Time] as Endtime,,

Broad Category as category

     [Time Band],

FROM

C:\Users\Harikrishna\Downloads\Test.xlsx

(ooxml, embedded labels, table is Sheet1);

from this information we have to calculate hour1,hour 2 ...hour 09 for each employee

time spent for each employee on hourly based....