Dynamic table sum

[#1]

Hi,

I have a question.

The OPERATOR string represents one of the table column name.

table:

LOAD * Inline

    [OPERATOR,COL1,COL2,

    COL1, 1, 2,

    COL2, 1, 2,

    COL1, 1, 2,

    COL2, 1, 2,

    COL2, 1, 2,

    COL1, 1, 2,

    COL1, 1, 2];

SET v1 = if(OPERATOR='COL1',COL1,

if(OPERATOR='COL2',COL2,0));

SET sum1 = sum($(zmienna));

I'm using sum1 in View. For the above example the correct sum1 is 10

Question:

How to write v1 and sum1 without using IF statement. My table has many more columns and writing 50 line if statement is bad idea.

[Kushal_Chawda]

You can use for loop to create the Variables

table:

LOAD * Inline

    [OPERATOR,COL1,COL2,

    COL1, 1, 2,

    COL2, 1, 2,

    COL1, 1, 2,

    COL2, 1, 2,

    COL2, 1, 2,

    COL1, 1, 2,

    COL1, 1, 2];

for i=1 to fieldvaluecount('OPERATOR')

let v_$(i) = fieldvalue('OPERATOR',$(i));

let v_sum_$(i) = 'Sum('&fieldvalue('OPERATOR',$(i)) &')';

Next i

Then on front end you can call the sum variable as $(=v_sum_1)

[#3]

Ok, but this example give me as many v_sum_x as rows in table. I just need one sum for all rows depending on operator column. More examples:

col1,1,2,

col1,1,2,

col1,1,2

the sum is 3 //(1+1+1)

col1,1,2,

col2,3,4,

col1,5,6,

col1,4,3

the sum is 14 //(1+4+5+4)

[Kushal_Chawda]

It will not give you  as many rows v_sum_x value, if Your operator field has 5 distinct value (Col1, Col2... Col5) then it will create 5 Variables v_sum_1,v_sum_2...v_sum_5

Here v_sum_1 = sum(COL1) , so $(=v_sum_1) =sum(COL1) will give you sum of all rows

[#5]

I don't think it's what i want.

I need only one variable with sum.  This sum has as many factors as rows. For each row I choose value from column name in OPERATOR.

OPERATOR | COL1 | COL2 | COL3                                                        | Choosed value

COL1          |     1     |    2    |    3                                                              | 1 from COL1

COL1          |      4    |    1    |    4                                                              | 4 from COL1

COL2          |      5    |    2    |    3                                                              | 2 from COL2 

COL1          |      8    |    2    |    1                                                              | 8 from COL1

COL3          |      1    |    6    |    3                                                              | 3 from COL3

COL2          |      6    |   7    |    2                                                              | 7 from COL2

so the sum is 1+4+2+8+3+6

[Kushal_Chawda]

try this

Data:

LOAD * Inline

    [OPERATOR,COL1,COL2,

    COL1, 1, 2,

    COL2, 1, 2,

    COL1, 1, 2,

    COL2, 1, 2,

    COL2, 1, 2,

    COL1, 1, 2,

    COL1, 1, 2];

Expression:

LOAD Distinct 'Rangesum('&concat(DISTINCT 'Sum({<OPERATOR={'&chr(39)&OPERATOR&chr(39)&'}>}'&OPERATOR&')',',')&')' as Col

Resident Data;

let vSum = Peek('Col',0,'Expression');

Now, you use the below as expression

$(=vSum)

[Kushal_Chawda]

attached application