Combine and Sum

[Nakia]

So,

I have in a FLD data like below that has 2 items (tab delimietd) per line with multiple lines

Example:
Adam 2.1
Adam 4.5
Bob 2.5
Bob 3.6
James 0.1

What I need to be able to do is loop through this FLD and combine all line entries that have the same 1st item and sum each of their second items!
So, looking at the above example I would end up with the following in the FLD
Example:
Adam 6.7
Bob 6.1
James 0.1


Any poionters?

[rkriesel]

Nakia --
Here's one way. Does it work for you?
-- Dick

Code: Select all

set the itemDelimiter to tab
repeat for each line tLine in field 1
    add item 2 of tLine to tSummary[ item 1 of tLine ]
end repeat
combine tSummary by cr and tab
sort tSummary

[Nakia]

works like a charm!!

Lets add another layer of complexity.
From this output I would like to convert the value into hours and minutes value with rounding up to the nearest 15 minute Interval.

Let say my output now is:
Adam 2.2

This would convert into 2 Hrs: 12 Minutes
and then with rounding
2 Hrs: 15 Minutes

[rkriesel]

Nakia --
Here's one way to combine and sum and round up.
For adding more layers of complexity, I suggest new threads with new subject lines.
-- Dick

Code: Select all

set the itemDelimiter to tab
repeat for each line tLine in field 1 -- note:  combine and sum
    add item 2 of tLine to tAccruedTime[ item 1 of tLine ]
end repeat
repeat for each key tPerson in tAccruedTime -- note:  round up
    put billableTime( tAccruedTime[ tPerson ] ) into tBillableTime[ tPerson ]
end repeat
combine tBillableTime by cr and tab
sort tBillableTime

Code: Select all

function billableTime pAccruedTime
    local tHours, tRemainder
    put trunc( pAccruedTime ) into tHours
    put pAccruedTime - tHours into tRemainder
    if tRemainder=0 then return tHours & " Hrs"
    if tRemainder<=.25 then return tHours & " Hrs: 15 Minutes"
    if tRemainder<=.5 then return tHours & " Hrs: 30 Minutes"
    if tRemainder<=.75 then return tHours & " Hrs: 45 Minutes"
    return tHours + 1 & " Hrs"
end billableTime