topic Re: Dynamic table sum in QlikView

Dynamic table sum

Wed, 25 Nov 2020 16:16:04 GMT

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,

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.

Re: Dynamic table sum

Kushal_Chawda — Mon, 28 Aug 2017 09:38:59 GMT

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,

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)

Re: Dynamic table sum

Mon, 28 Aug 2017 10:19:45 GMT

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

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)

Re: Dynamic table sum

Kushal_Chawda — Mon, 28 Aug 2017 11:42:37 GMT

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

Re: Dynamic table sum

Mon, 28 Aug 2017 12:14:15 GMT

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

Re: Dynamic table sum

Kushal_Chawda — Mon, 28 Aug 2017 14:17:59 GMT

try this

Data:

LOAD * Inline

[OPERATOR,COL1,COL2,

COL1, 1, 2,

COL2, 1, 2,

COL1, 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)

Re: Dynamic table sum

Kushal_Chawda — Mon, 28 Aug 2017 14:20:33 GMT

attached application