13 Replies Latest reply: Feb 19, 2014 3:51 PM by Natalie Law RSS

    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

          • 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

              • Re: How to calculate consecutive work days?
                Gysbert Wassenaar

                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?

                    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?

                      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

                          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?

                              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

                                  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?

                                      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?

                                        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

                                            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?

                                                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?

                                                  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