topic Re: How to calculate consecutive work days? in QlikView

How to calculate consecutive work days?

Thu, 29 Nov 2012 07:47:46 GMT

Hi all,

The following is an example of how my table looks like. Now i would like to hightlight out the staff who worked for X number of consecutives days.

Maybe a field to show between 21 to 23 what is the consecutive numbers of days they worked. or any other suggestion?

Entry

EntryDate	StaffName	TimeIn	Message
21/11/12	John	07:00:00	IN
21/11/12	Peter	08:10:00	IN
21/11/12	John	09:20:00	IN
21/11/12	Mary	07:20:00	IN
22/11/12	John	07:00:00	IN
22/11/12	Peter	08:00:00	IN
23/11/12	John	07:00:00	IN
23/11/12	Mary	07:00:00	IN
23/11/12	Joe	08:00:00	IN
23/11/12	Mary	09:00:00	IN

Result:

John : 3

Mary: 0

Peter: 2

Joe: 0

And how can i prompt user to enter the value for X. So if they click 3, John will be highlighted and 2 Peter will be highlighted.

Guidance please.

Thanks,

10e5x

Re: How to calculate consecutive work days?

Gysbert_Wassenaar — Thu, 29 Nov 2012 08:45:50 GMT

See attached qvw

Re: How to calculate consecutive work days?

Thu, 29 Nov 2012 08:56:23 GMT

Why qlikview do not allow me to open your attached?

Message showing:

...document created by another qlikview personal edition user...since you are using QlikView Personal edition, you may only open document created by yourself....

Should i continue with opening, the warning say if i continue i may be unable to open the documents created by my current user key

Re: How to calculate consecutive work days?

Gysbert_Wassenaar — Thu, 29 Nov 2012 09:10:33 GMT

You're using the Personal Edition, so you can't open files created by others.

Create a new file and use the script below to load the data. Then add two listboxes for StaffName and Counter. If you select a value in Counter (the no. consecutive days) you'll see the matching StaffNames.

Temp:

load

date#(EntryDate,'DD-MM-YYYY') as EntryDate,

StaffName,

timestamp#(TimeIn,'hh:mm:ss') as TimeIn,

Message;

LOAD * INLINE [

EntryDate, StaffName, TimeIn, Message

21-11-2012, John, 7:00:00, IN

21-11-2012, Peter, 8:10:00, IN

21-11-2012, John, 9:20:00, IN

21-11-2012, Mary, 7:20:00, IN

22-11-2012, John, 7:00:00, IN

22-11-2012, Peter, 8:00:00, IN

23-11-2012, John, 7:00:00, IN

23-11-2012, Mary, 7:00:00, IN

23-11-2012, Joe, 8:00:00, IN

23-11-2012, Mary, 9:00:00, IN

];

Data:

load *,

if(rowno()=1 or StaffName <> previous(StaffName),1,

if(EntryDate-previous(EntryDate)=1, peek('Counter')+1,peek(Counter))) as Counter

Resident Temp order by StaffName, EntryDate;

Re: How to calculate consecutive work days?

Thu, 29 Nov 2012 09:16:40 GMT

It works i will digest your scripts and use it for my context, thanks so much. Anyway if you are free, could u help me with the previous post u just answered in my thread Not showing assoicated values, i have some doubts...

Re: How to calculate consecutive work days?

Thu, 29 Nov 2012 09:20:40 GMT

Hi,

Thanks althought it works by creatng a new document and pasting your scripts in it, but it does not work form me. When i modify your scripts to my context, the counter field appear but only with values1... Why is this so?

Btw mind explaining the last part? starting from if(rowno.....

Very difficult to uds myself and i want to learn the scriptings

Please

Appreciated,

Guoxiang

Re: How to calculate consecutive work days?

Gysbert_Wassenaar — Thu, 29 Nov 2012 09:40:08 GMT

The previous and peek functions can be used to look in previous records. Previous looks in the record set you're loading from and peek looks in the table you're loading into.

If we are on the first record or we find a new Staffname we start the counter on 1

if(rowno()=1 or StaffName <> previous(StaffName),1,

If we find a new date we check if it is 1 day later

if(EntryDate-previous(EntryDate)=1,

If it is 1 day later we increase the counter by 1

peek('Counter')+1,

If it is not 1 day later we don't increase the counter, but use its current value

peek(Counter))) as Counter

Re: How to calculate consecutive work days?

Thu, 29 Nov 2012 09:43:49 GMT

I have tried various editing i still get the counter as 1. I have 3 dates, shouldnt it be 3? Can u help me with that?

Re: How to calculate consecutive work days?

Gysbert_Wassenaar — Thu, 29 Nov 2012 14:11:56 GMT

You have a lot more fields, so it's possible the order isn't right yet to calculate the counters. I see in your qvw document you didn't include StaffName in the order by clause. Maybe if you add it to the order by clause things will go better.

Re: How to calculate consecutive work days?

Thu, 29 Nov 2012 14:31:20 GMT

Thanks for the reply but sorry i really dont get what u mean. add StaffName to the order by clause things will go better. i tot the staffName is already included in the order by? i try edit myself but still stuck at counter equals only 1. I look at table viewer but still cant uds what u trying to teach.... example or details please?

Or if u need, i can cut down on some of the field

Re: How to calculate consecutive work days?

Sun, 02 Dec 2012 14:26:18 GMT

Hi Gysbert,

I have included the staffName but it doesnt change for the better. Seriously need your advise. I am stuck on this problem for many days. Please help me.

Thanks,

10e5x

Re: How to calculate consecutive work days?

Gysbert_Wassenaar — Sun, 02 Dec 2012 15:21:13 GMT

Two things:

1. make sure the order by clause is order by StaffName, EntryDate, not order by EntryDate, StaffName

2. make sure EntryDate is recognized as a date. To be sure you can use date#(EntryDate,'DD/MM/YYY') as EntryDate in the load statement

Another thing, you may want to calculate the counter first using T1, then drop T1 and join Data on the second SQL load with the legal exits. You now have a couple of unnecessary joins which is kinda messy .

Entry2:

Load

EventDate,

date#(EntryDate,'DD/MM/YYYY') as EntryDate,

StaffName,PassNum,Company,TimeIn ;

SQL Select

`EVENT_D` AS EventDate,

`EVENT_D` AS EntryDate,

`PASS_M` AS StaffName,

`PASS_N` AS PassNum,

`COMPANY_M` AS Company,

Min(EVENT_T) as TimeIn

From `TACS_ACCESS_DTL`

WHERE `MESSAGE_X` Like '%Legal Access%'

group by `EVENT_D`, `PASS_M`, `PASS_N`, `COMPANY_M`;

Data:

load *,

if(rowno()=1 or StaffName <> previous(StaffName),1,

if(EntryDate-previous(EntryDate)=1, peek('Counter')+1,peek(Counter))) as Counter

Resident Entry2 order by StaffName,EntryDate;

Drop table Entry2;

join (Data)

Load

SQL Select

`EVENT_D` AS EventDate,

`EVENT_D` AS ExitDate,

`PASS_M` AS StaffName,

`PASS_N` AS PassNum,

`COMPANY_M` AS Company,

Max(EVENT_T) as TimeOut

From `TACS_ACCESS_DTL`

WHERE `MESSAGE_X` Like '%Legal Exit%'

group by `EVENT_D`, `PASS_M`, `PASS_N`, `COMPANY_M`;

Re: How to calculate consecutive work days?

Mon, 03 Dec 2012 01:16:01 GMT

THANKS gysbert, it works. the counter 3 is out. Although the selection is weird(not i wanted but its good enough, i shall modify from here)

I am looking at the script and table to uds how u manage to did that. i realise that my dates are not in the date format. Thanks for that nice advice.

May I come to you if i need further help?

Appreciated,

10e5x

Re: How to calculate consecutive work days?

Wed, 19 Feb 2014 20:50:59 GMT

Hi Gysbert,

This is very close to the solution that I am looking for and wondered if you could help me? Rather than calculate consecutive days I want to identify instances of consecutive days. Not sure if it's etiquette to add the post here and have added previously but had no takers and your solution seems very close.

I sent you a friend request and hope to hear from you.

Many thanks

Natalie