calculate(script or expression) spended time

[#1]

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

[Anonymous]

Hi Kristian,

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

[Anonymous]

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