The Peek() function looks back to the last record. I need something that Peek()s forward. I have a start date in my records and that's data is valid until my next start date in my next applicable record. So I need to order them appropriately then "look forward" to the next record to get my "valid end date" so to speak.
Does anyone know if there's some sort of Next() style function? Or because the data is loading a row at a time, is the data not there to be able to do that?
There is no "forward" looking function. Peek() can only consider any values in a field and table that has already been loaded and are in memory. And yes the load is done on processing-wise on a row by row basis.
For you to be able to process in the way you want the most generic way of handling it is by using a load that reads from an in-memory table. A so-called RESIDENT load.
A GROUP BY clause is often a handy way of getting start dates and or end dates. The resident load will also suppor sorting of you data which the normal FROM load will not.
Oh...this is clever! My company does a lot of tables where there is a from date and the to date is the next applicable "from". Great for consistency on our software but less good for me as a reporting person. Haha.