Hi Guys,
Is there a way to return the second or third next or previous record values using the get next/previous functions or perhaps a custom function to do that.
I am trying to match 2/3 records in sequence and also return a value based on the 2/3 records in sequence as such i had employed the get next/previous functions.I want to return the second/third next or previous record value whereas the get next / previous value functions only allows me to return the immediate next or previous value.
e.g.
ACCOUNT NO. INT. RATE FORMULA EXPECTED RESULT
1234 10
2345 6
2345 13
2345 25 @GetPreviousValue("@GetPre**** INT. RATE 6
What i had tried to do was, @GetPreviousValue("@GetPreviousValue ("ACCOUNT_NO.")") to try and get the second previos record value (ACCOUNT NO. 2345, INT RATE 6) but it didnt not work.
Please help
ThDon
Offline
Last seen: 8 years 3 months ago
Joined: 05/17/2015 - 11:59
Hi ThDon,
Unfortunately you can't use the @GetPrev or @GetNext to skip records. You could do this through a script but another way you might want to try is this. Now can I assume you are tyring to get the lowest or largest interest rate to perform some type of calculation? If so sort your file by Account No and Interest Rate. Now do a summary by Account No adding the Interest Rate field, select the first instance to get the smallest interest rate or last instance to get the largest. You will now have a summary file with one acc0unt number and either the smallest or largest interest rate. Now join this file back to the original file using the account no as the match. You now have a file that has a field with either the lowest or largest interest rate to do your calculation. Let me know if this is what you are looking for and if this is not it what result you are looking to obtain.
Thanks
Brian