2 Replies Latest reply: Oct 15, 2012 2:22 AM by Jonathan Brough RSS

    calculate(script or expression) spended time

    Kristian König

      Hello again,

       

      i have this script:

       

      Obilasci:

      LOAD EMPLID as KOM,

           Date as [Obilasci datum],

           MonthName(Date) as [Obilasci mjesec], 

           Br as [Obilasci redni broj],

           Km as [Obilasci stanje KM],

           Dol as [Obilasci dolazak], TIME OF ARRIVAL (format HH:MM)

           Odl as [Obilasci odlazak], TIME OF DEPARTURE (format HH:MM)

           V as [Obilasci učinak],//P = Početak rada, - = bez zaklj. prodaje, + = prodaja, K = Kraj radnog vremena, O = Ostalo(Orsy, sastanak firma, pauza, itd...)  

           Šifra as [Obilasci PP],

           Naziv as [Obilasci naziv PP]

      FROM

      Y:\obilasci_posjete_kilometri.xlsx

      (ooxml, embedded labels, table is obilasci_posjete_kilometri)

      where Date>='01.11.2011';

       

      The table gives me:

      Partner number - time of arrival - time of departure - mileage...

       

      I got 2 questions, hope for at least one an answer

       

      1.I dont know how to calculate the time spend at one partner(i tried simple ODL-DOL in the script but there are no "normal" results.)

      2. In this table are shown all visits where Date>='01.11.2011'; for every employee. Mileage is the number they have to report when arriving to a partner. Is there a chance i could extract(in script or expression) the driven mileage in one month? (Oldest mileage value - newest mileage value)?

       

      Thank you in advance,

       

      Greetigns Kristian

        • Re: calculate(script or expression) spended time
          Johannes Sunden

          Hi Kristian,

           

          You should be able to use Interval(ODL-DOL,'hh:mm') to get the time spent.

            • Re: calculate(script or expression) spended time
              Jonathan Brough

              Oldest and newest mileage can be found by adding another Load with a Group By clause that groups the table according to the Employee ID and the Month. Within this Load you can do a min(Date) and a max(Date) to find their first and last records for that employee. This would be something like:

               

              MinMaxDates:

              LOAD

                   KOM,

                   Min(Date) as EmpMonthMinDate,

                   Max(Date) as EmpMonthMaxDate,

              RESIDENT Obilasci

              GROUP BY [Obilasci mjesec], KOM;

               

              You would then need to join these MinDate and MaxDate fields from the new table onto the original table:

               

              LEFT JOIN (Obilasci) LOAD

                  KOM,

                  [Obilasci mjesec]

                  EmpMonthMinDate,

                  EmpMonthMaxDate

              RESIDENT MinMaxDates;

               

              Be sure there are no exact combinations of dates by testing how many records there are in Obilasci before and after this join. If there are more then you may have to add a key to the table or do some more groupings.

               

              Finally, with everything on the same table, reload the table and have a test for which records are the min and max dates for an employee, with the milage being saved as a new field:

               

              NewObilasci:

              LOAD

                  *,

                  if(EmpMonthMinDate,[Obilasci stanje KM]) AS EmpMonthStartKm,

                  if(EmpMonthMaxDate,[Obilasci stanje KM]) AS EmpMonthEndKm

              RESIDENT Obilasci;

               

              Note, you could add a further Join to calculate the difference betwee start and end for the month's mileage.

              Jonathan