Solved: Re: How to calculate consecutive work days? - Qlik Community

Report Inappropriate Content · ‎2012-11-29

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

Gysbert_Wassenaar · ‎2012-12-02

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`;

talk is cheap, supply exceeds demand

View solution in original post

Gysbert_Wassenaar · ‎2012-11-29

See attached qvw

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2012-11-29

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

Gysbert_Wassenaar · ‎2012-11-29

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;

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2012-11-29

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

Report Inappropriate Content · ‎2012-11-29

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

Gysbert_Wassenaar · ‎2012-11-29

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

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2012-11-29

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?

Gysbert_Wassenaar · ‎2012-11-29

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.

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2012-11-29

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