Evaluate calculation on script

[sela]

Dear All

I have a few different formulas.
Every single formula needs to calculate different group on the same table.
For example, the formula for Group-1 is 'Sum(Fact1)' and the formula for Group-1 is 'Sum(Fact2)'

Group	Fact1	Fact2
Group-1	1	3
Group-1	1	3
Group-2	2	4
Group-2	2	4

There are an unknown number of groups and formulas.
I try to ' Evaluate' this calculation by using variables for each formula,
and call dynamically to the related variable to the relevant group, but It didn’t worked:

SET vFormula_Group-1 = 'Sum(Fact1)';
SET vFormula_Group-2 = 'Sum(Fact2)';

LOAD
Group
,Evaluate('vFormula_' & Group) AS Formula

I also try to 'Evaluate' by '$' sign:

$(vFormula_)

Is it possible?
Thanks for help,

Sela

[#2 · Accepted solution]

Hello Sela,

It is possible using variables with parameters.

Attaching sample app for you.

Code:

SET vFormula = "if($1 = 'Group-1', Sum(Fact1), if($1 = 'Group-2', Sum(Fact2)))";

LOAD
Group
,$(vFormula(Group)) AS Formula
Group By Group
;
LOAD * INLINE [
    Group, Fact1, Fact2
    Group-1, 1, 3
    Group-1, 1, 3
    Group-2, 2, 4
    Group-2, 2, 4
];

Result I received:

Hope it helps!

BR,

Kuba

[sela]

Thank you Jakub,

Your solution solve my case perfectly!!!