Use variable dynamically as function

[yvesqlik]

I have a set of variable

var1= $1+1;

var2=$1+2;

var3=$1+3;

…

my table

A	Var_Index	Value
X	1	10
Y	2	100
Z	3	1000

I like to create a NewColumn that depends from the columns ‘Var_Index’ and ‘Value’

For example, the first line Var_Index=1, Value=10 then NewColumn=$(var1(10))=10+1=11

A	Var_Index	Value	NewColumn
X	1	10	11
Y	2	100	102
Z	3	1000	1003

Load

            A,

            Value,

            $(var&Var_Index(Value)) as NewColumn

Resident Table;

My code doesn’t work, how can I modify it that it works.

I try to avoid pick(Var_Index,…) and  If(Var_Index=….) because my list of variable is longer than this sample and I create the variable dynamically.

Thanks for your help and answer.

Nice Week End!

[Gysbert_Wassenaar]

SET var1= $1+1;

SET var2= $1+2;

SET var3= $1+3;

Data:

LOAD *, pick(Var_Index, $(var1(Value)), $(var2(Value)), $(var3(Value))) as NewColumn INLINE [

    A, Var_Index, Value

    X, 1, 10

    Y, 2, 100

    Z, 3, 1000

];

[yvesqlik]

pick(Var_Index, $(var1(Value)), $(var2(Value)), $(var3(Value)))

this solution is not good because the variable will be create automatically

[Gysbert_Wassenaar]

Then create the pick expression automatically too.

[marcus_sommer]

Could you give a more descriptive example and why you need for this variables - maybe another approaches are more suitable, maybe: rangesum(Var_Index, Value) as NewColumn.

- Marcus

[yvesqlik]

It was tricky but i get it...

Thanks!