topic String Calculation Evaluation in QlikView

String Calculation Evaluation

Tue, 09 Feb 2010 16:48:27 GMT

Hi,

I have a field in a SQL table that contains a string representing a calculation. I.e.:

item	calc
A	20+10+2
B	15+3

In the example, for Item A, '20+10+2' is the text value of field 'calc'

I need to evaluate in the script this calculation to another field with the result ( 32 for item A, 18 for item B).

I wanted to do that in the LOAD or SELECT sentence, avoiding further loops inside each record. I have tried both Evaluate function and $ expansion, but both don't work, resulting in a script crash. I understood that Evaluate function does not accept string value.

Any ideas?

Thanks

Paolo

String Calculation Evaluation

disqr_rm — Tue, 09 Feb 2010 17:00:47 GMT

Check Evaluate() function. Here is a sample for you:


SourceTab:
LOAD * INLINE [
item, calc
A, 20+10+2
B, 15+3
];
TargetTab:
Load item,
 evaluate(calc) as calc_result
resident SourceTab;

String Calculation Evaluation

Wed, 10 Feb 2010 23:49:57 GMT

Actually, this is the first solution I tried, but the script fails. I suspect this is because this field is coming from a SQL table as a varchar and this is not the correct use of evaluate() function.

In fact, evaluate(20+10+2) is working, but evaluate(calc), where calc='20+10+2' does not.

String Calculation Evaluation

disqr_rm — Wed, 10 Feb 2010 23:59:06 GMT

I see. Well you can try to play with the contents ad see how it goes. I would try somethinglike below:


Data:
Load item,
evaluate(trim(text(calc))) as calc_result;
SELECT * from SqlTable;

String Calculation Evaluation

Thu, 11 Feb 2010 11:39:57 GMT

This is not working as well, I keep trying with every combination...

Thanks

String Calculation Evaluation

Mon, 14 Nov 2011 16:40:45 GMT

in qv 10 sr3 it is now working with the following:

let vTest = 1;

Calc:

load *, Evaluate(formula) as EvalCalc INLINE [

Key;formula

1; vTest+10+2

2; 15+3

](delimiter is ';');

Which evaluates to 13 and 18 in a table view.

But what if we want to evaluate field values? has anyone succeeded?

ex;

let vTest = 1;

Calc:

load *, Evaluate(formula) as EvalCalc INLINE [

Key;formula

1; vTest+10+2

2; 15+3

3; Key+10+2

](delimiter is ';');

The field Key in the fomula will not evaluate to 3 but instead shows nothing.

Re: String Calculation Evaluation

swuehl — Mon, 14 Nov 2011 16:49:15 GMT

We are just discussing this problem here:

http://community.qlik.com/message/164822

My suggestion would be to use replace function to put the field values into EvalCalc string before calling evaluate().

Hope this helps,

Stefan

edit:

In the meanwhile, much better solutions were posted, I redraw my suggestion 😉

String Calculation Evaluation

Tue, 15 Nov 2011 11:42:27 GMT

Great! Thanks, i got it working with some hints from that post. We can pick the formula we want dynamically in the script. I chose the 2nd formula in pick just for testing.

vartest:

LOAD * INLINE [

id, field1, field2, calc

1 , 1 , 10 , field1/field2

2 , 2 , 20 , field1*field2

3 , 3 , 30 , field1+field2

];

let i=0;

let fxVarEvalN = fieldValue('calc',1);

for i = 2 to NoOfRows('vartest')

fxVarEvalN = fxVarEvalN & ',' & fieldValue('calc',$(i))

next i

vartest2:

load id, pick(2, $(fxVarEvalN)) as pickedCalc;

load field1,field2,id

Resident vartest;

Re: String Calculation Evaluation

apsafos — Fri, 01 Nov 2019 11:30:44 GMT

Thank you very much, it was really helpful.