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]

At least, Provide Formula for Hours where you getting in Excel ?

[Anonymous]

Hi Anil,

I have attached qvw along with xl sheet ,where u can find hours calculation ....

[Anil_Babu_Samineni]

Looks like you missed attachment which is updated because, Still the date showing is 19th only. Will you??

[Anonymous]

can u pls look at  main thread ,

let me know if u haave issue still again ...

[Anil_Babu_Samineni]

Yes, Still it is there? Will you copy the expression for Hours on wall. May be that may trigger the issue

[Anonymous]

Temp:
LOAD Team as Empname,
Region,
SPOC,
Shadowee,
Date,
[Start Time] as Startrime,
[End Time] as Endtime,
num((time(([End Time]-[Start Time]),'hh.mm.ss')*86400)/60,'##0.##') as time,
[Time Band],
[Broad Category] as Use,
[Broad Category (Old)],
[Task Sub Category],
[Task Sub Category (Old)],
[Function Sub Category (New)],
[Function Sub Category (old)],
[Task Description],
Hotspot,
[Paper Notes],
Comments,
Questions,
[Time Spent],
[Day Start Time]
FROM
C:\Users\Harikrishna\Downloads\Test.xlsx
(ooxml, embedded labels, table is Sheet1);




Time:
Left Join(Temp)
LOAD
Date,
Empname,
time(min(Startrime)) as minhour,
time(max(Endtime)) as maxhour,
time(time(min(Startrime))+MakeTime(1,0,0)) as Hour1,
time(time(min(Startrime))+MakeTime(2,0,0)) as Hour2,
time(time(min(Startrime))+MakeTime(3,0,0)) as Hour3,
time(time(min(Startrime))+MakeTime(4,0,0)) as Hour4,
time(time(min(Startrime))+MakeTime(5,0,0)) as Hour5,
time(time(min(Startrime))+MakeTime(6,0,0)) as Hour6,
time(time(min(Startrime))+MakeTime(7,0,0)) as Hour7,
time(time(min(Startrime))+MakeTime(8,0,0)) as Hour8,
time(time(min(Startrime))+MakeTime(9,0,0)) as Hour9,
time(time(min(Startrime))+MakeTime(10,0,0)) as Hour10,
time(time(min(Startrime))+MakeTime(11,0,0)) as Hour11,
time(time(min(Startrime))+MakeTime(12,0,0)) as Hour12
Resident Temp  Group by Date,Empname;






final:
LOAD  *,
num((time((maxhour-minhour),'hh.mm.ss')*86400)/60,'##0.##') as timenew,
time(maxhour-minhour) as newtimes,
if(Startrime>=minhour and Startrime<Hour1,'1 hour',if(Startrime>=Hour1 and Startrime<Hour2,'2 hour',if(Startrime>=Hour2 and Startrime<Hour3,'3 hour',
if(Startrime>=Hour3 and Startrime<Hour4,'4 hour',if(Startrime>=Hour4 and Startrime<Hour5,'5 hour',if(Startrime>=Hour5 and Startrime<Hour6,'6 hour',
if(Startrime>=Hour7 and Startrime<Hour8,'8 hour',if(Startrime>=Hour8 and Startrime<Hour9,'9 hour',if(Startrime>=Hour9 and Startrime<Hour10,'10 hour',
if(Startrime>=Hour10 and Startrime<Hour11,'11 hour',if(Startrime>=Hour11 and Startrime<Hour12,'12 hour'))))))))))) as Hours,
Interval('00:01:00',1) as interval

Resident Temp;
DROP Table Temp;


Left Join(final)
LOAD Date,Hours,Empname,
time(min(Startrime)) as minstarttime,
time(max(Startrime)) as maxstarttime
Resident final Group by Date,Hours,Empname;

[Anil_Babu_Samineni]

This code in Qlikview and you are talking about Excel? I am confused again. Wait for a while mrkachhiaimp‌ kush141087‌ may reply as you aware

[MK_QSL]

Busy with my job project.. But yes, I did similar thing in one of my project so can try the same here.
I will look into this during weekend time. Meantime, let's see if someone can come back with an answer.

Regards,

MK

[Kushal_Chawda]

The code which you have paste is working or not? I am still not understanding the logic to calculate Hour1 and Hour 2. Can you please take one example from excel and explain?

[Anonymous]

Hi Kushal,

if u dont mine can u give personal number,so that i will explain exact requirement