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

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.

• ###### 1. Re: Creating a calculated field that 'looks up' the value of a column from the inputted value of another column

Hey Michael,

Have you tried something like:

{ FIXED [Clm Clmt Covg Key] : SUM(

IF [Transaction Number] = 1

THEN [Reserve Increase]

END

) }

The aggregation SUM() won't actually change the value of [Reserve Increase] (unless there is more than one row where [Transaction Number] = 1), but the FIXED expression requires an aggregation.