Accumulation of certain variables as other variable

[#1]

Dear Community,

I've got following issue

Imagine following table structure

I want to create a new variable 'C4' in my loading script that accumulates the 'benefit'-values when variable 'Cat' has value 'C2' or 'C3'

The final result should look like this:

I tried using if-statements, but never with the right result.

Thanks in advance for you help

[Gysbert_Wassenaar · Accepted solution]

Something like this:

Load Site, if(Cat='C2' or Cat='C3', 'C4', Cat) as Cat, Benefit

from ...;

You can simply sum Benefit in an expression in the Front End. If you want to do that in the script you do it like this:

Load Site, if(Cat='C2' or Cat='C3', 'C4', Cat) as Cat, sum(Benefit) as Benefit

from ...

group by Site, Cat;