Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How to calculate consecutive work days?

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


EntryDateStaffNameTimeInMessage
21/11/12John07:00:00IN
21/11/12Peter08:10:00IN
21/11/12John09:20:00IN
21/11/12Mary07:20:00IN
22/11/12John07:00:00IN
22/11/12Peter08:00:00IN
23/11/12John07:00:00IN
23/11/12Mary07:00:00IN
23/11/12Joe08:00:00IN
23/11/12Mary09:00:00IN

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: How to calculate consecutive work days?

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

13 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: How to calculate consecutive work days?

See attached qvw


talk is cheap, supply exceeds demand
Not applicable

Re: How to calculate consecutive work days?

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

MVP & Luminary
MVP & Luminary

Re: How to calculate consecutive work days?

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
Not applicable

Re: How to calculate consecutive work days?

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

Not applicable

Re: How to calculate consecutive work days?

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

MVP & Luminary
MVP & Luminary

Re: How to calculate consecutive work days?

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
Not applicable

Re: How to calculate consecutive work days?

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?

MVP & Luminary
MVP & Luminary

Re: How to calculate consecutive work days?

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
Not applicable

Re: How to calculate consecutive work days?

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