Evaluate and create a new record using For-Next Loop

[#1]

Table available:

Key	ConnectionId	CuoffTime	TransitTime
1	a1	9:00	5
1	a2	11:00	8
2	b1	5:00	3
2	b2	13:00	6
2	b3	17:00	7

For each distinct key, I wish to calculate a metric based on some logic involving cutoffTime and TransitTime for each connectionid and store the max of the metric for each key. Number of connectionIds are variable for each key.

Can anybody help out as to how to go about it...

[marcus_sommer]

I think you will need two loads. First a load with a calculation like rangesum(CutoffTime, TransitTime/24) as NewField and in the second load use a max(NewField) as MaxnewField as aggregation over your key.

- Marcus

[#3]

Thanks Marcus for such prompt reply. The calculation of metric requires me to fetch the cutoff time for the next connectionid for a particular key.

I'll be more specific. The metric is SLA (waiting time for an available connection + transit time for the connection). For Eg.

SLA_Key1_ConnectionId1 = (11:00 - ( 9:00 - (90 mins/ 60) + 5 ) hrs. Here 90 mins is the buffer time during which an incoming packet misses the connection.

[#4]

Correction *In SLA_Key1_ConnectionId1 = (11:00 - ( 9:00 - (90 mins/ 60) + *8*) hrs. since its going by 2nd connection.

[marcus_sommer]

If you need for the calculation to look to another row/key you could use previous() and/or peek() in a proper sorted table. If it is more complicated you could also use peek() or lookup() from outside in a loop to create the final table - but you will definitely need some logic to identify your rows.

- Marcus