Page 1 of 1
Combine and Sum
Posted: Wed Aug 07, 2013 11:53 pm
by 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?
Re: Combine and Sum
Posted: Thu Aug 08, 2013 2:21 am
by 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
Re: Combine and Sum
Posted: Thu Aug 08, 2013 3:40 am
by 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
Re: Combine and Sum
Posted: Thu Aug 08, 2013 8:12 am
by 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