Comparing value in different rows and columns, combining rows

[shuck1985]

I have a table with the following columns

case number       Timestamp Beginning       Timestamps End     Total Time

I want to do the following: if the case number is the same then compare end time of row 1 and beginning time of row 2. If the difference is less than 5mins, create a new row with case number, beginning timestamp of row 1, end timestamp of row 2 and total time (sum of row 1 and row 2 total time).

currently I am unsure how to even start so any hint or idea would be greatly appreciated.

Thanks in advance

[marcus_sommer]

Within an ordered load you could with interrecord-functions like Peek() or Previous() ? check the values from other records to calculate anything and/or to create new fields. I think in your case you could create your needed/changed values within one ore more new fields and also setting a flag of 0 or 1 to identify your needed records.

After this you load this table resident by using the flag and concatenate the records to it again by replacing the origin fields with the new created ones.

- Marcus

[shuck1985]

thank you

I'll give that a shot and report back later.