topic Evaluate and create a new record using For-Next Loop in QlikView

Evaluate and create a new record using For-Next Loop

Tue, 12 Aug 2014 10:48:07 GMT

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...

Re: Evaluate and create a new record using For-Next Loop

marcus_sommer — Tue, 12 Aug 2014 10:54:21 GMT

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

Re: Evaluate and create a new record using For-Next Loop

Tue, 12 Aug 2014 11:18:05 GMT

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.

Re: Evaluate and create a new record using For-Next Loop

Tue, 12 Aug 2014 11:20:30 GMT

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

Re: Evaluate and create a new record using For-Next Loop

marcus_sommer — Tue, 12 Aug 2014 11:32:46 GMT

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