Referencing a different data output in another column

[ehmacc]

Rather new to Cognos, and have found the Q&A on this site helpful- trying to referencing a different line's value in a new column

Trying to determine how to create a query that reference the value on a separate line

For instance- if a employee signs up for spouse life insurance, I need to see the employee amount next to the spouse amount. If Benefit=Spouse Life, then give Value for Benefit= Vol. Life, else null type of thing:

Employee   Benefit         Value         Query
1234           Vol. Life          $10         (null)
1234           Spouse Life       $7           $10

[khabaleshri]

you can create 2 data items to achieve your scenario.
assuming every employee has 2 benefits only i.e Vol. life and Spouse life
1. dataitem1 if Benefit=Spouse Life then 0 else value.
2. dataitem2 if Benefit=Spouse Life then maximum(dataitem1 for employee) else null

this solution will slower the performance but it should work.