1 Reply Latest reply on Apr 4, 2016 5:29 PM by Esther Aller

    Creating a calculated field that 'looks up' the value of a column from the inputted value of another column

    Michael Lomas

      http://imgur.com/xsuRVDi - photo of table


      I have a table that I'm using to test some calculated fields I'm creating. The table shows transactions sorted by the pseudo claim numbers on the left side column labeled 'Clm Clmt Coverage Key'. I've used the FIXED() function to create a few columns based on the [Reserve Increase] and [Transaction Number] columns. For example:


      [Transactions per Claimant] = {FIXED [Clm Clmt Covg Key] : CountD([Transaction Number])}

      [Max Reserve Increase] = {FIXED [Clm Clmt Covg Key] : Max([Reserve Increase])}

      [End Reserve] = {FIXED [Clm Clmt Covg Key] : Max([Cume Reserve])}


      Now what I'd like to do is identify the first [Reserve Increase] (ie [Transaction Number] = 1) in all rows that relate to a specific claim number. In theory the logic for the calculated field would be something like 'for each claim number, look up the Reserve Increase amount associated with the Transaction Number = 1 and print that for all rows associated with that particular claim'. I cant use the MIN() [Reserve Increase] with a FIXED() [Clm Clmt Covg Key] as the first reserve isn't necessarily the smallest reserve.


      Thanks in advance!