String Calculation Evaluation

[#1]

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

[disqr_rm]

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;

[#3]

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.

[disqr_rm]

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;

[#5]

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

Thanks

[#6]

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.

[swuehl]

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 😉

[#8]

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;

[apsafos]

Thank you very much, it was really helpful.