topic Re: Crazy reuirement in qlik life for time calculation? in QlikView

Crazy reuirement in qlik life for time calculation?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 .....

Re: Crazy reuirement in qlik life for time calculation?

Anil_Babu_Samineni — Wed, 19 Jul 2017 16:14:34 GMT

Can you read this? Buckets

Re: Crazy reuirement in qlik life for time calculation?

Anonymous — Thu, 20 Jul 2017 10:27:16 GMT

Can u pls look into this , am not understanding

Re: Crazy reuirement in qlik life for time calculation?

Anil_Babu_Samineni — Thu, 20 Jul 2017 11:57:40 GMT

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

Re: Crazy reuirement in qlik life for time calculation?

arvind_patil — Thu, 20 Jul 2017 12:04:19 GMT

Hi ,

Please tell us expected result by providing the sample Excel.

Thanks,

Arvind Patil

Crazy reuirement in qlik life for time calculation?

Re: Crazy reuirement in qlik life for time calculation?

Anil_Babu_Samineni — Thu, 20 Jul 2017 12:18:22 GMT

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

Re: Crazy reuirement in qlik life for time calculation?

Anonymous — Thu, 20 Jul 2017 13:08:15 GMT

Yes Exactly Anil

Re: Crazy reuirement in qlik life for time calculation?

Anonymous — Thu, 20 Jul 2017 13:09:36 GMT

I have attached xl file

pls see the sheet2 for out put

Re: Crazy reuirement in qlik life for time calculation?

Anil_Babu_Samineni — Thu, 20 Jul 2017 13:11:42 GMT

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

Re: Crazy reuirement in qlik life for time calculation?

Anonymous — Thu, 20 Jul 2017 13:34:42 GMT

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....

Re: Crazy reuirement in qlik life for time calculation?

Anil_Babu_Samineni — Thu, 20 Jul 2017 13:48:35 GMT

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

Re: Crazy reuirement in qlik life for time calculation?

Anonymous — Fri, 21 Jul 2017 02:09:30 GMT

Hi Anil,

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

Re: Crazy reuirement in qlik life for time calculation?

Anil_Babu_Samineni — Fri, 21 Jul 2017 02:57:55 GMT

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

Re: Crazy reuirement in qlik life for time calculation?

Anonymous — Fri, 21 Jul 2017 04:27:55 GMT

can u pls look at main thread ,

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

Re: Crazy reuirement in qlik life for time calculation?

Anil_Babu_Samineni — Fri, 21 Jul 2017 04:39:30 GMT

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

Re: Crazy reuirement in qlik life for time calculation?

Anonymous — Fri, 21 Jul 2017 04:43:01 GMT

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;

Re: Crazy reuirement in qlik life for time calculation?

Anil_Babu_Samineni — Fri, 21 Jul 2017 05:21:44 GMT

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

Re: Crazy reuirement in qlik life for time calculation?

MK_QSL — Fri, 21 Jul 2017 06:21:23 GMT

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,

Re: Crazy reuirement in qlik life for time calculation?

Kushal_Chawda — Fri, 21 Jul 2017 07:10:52 GMT

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?

Re: Crazy reuirement in qlik life for time calculation?

Anonymous — Fri, 21 Jul 2017 15:10:25 GMT

Hi Kushal,

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