Interpret field values as formulas

[smiling_cheetah]

Hello everyone, 
I'm trying to calculate a field in script based on the formula written in another field, and would really appreciate your help 

The table looks like this: 

Tab:
Load * inline [
id, field1, field2, formula
1, 100, 150, field1*1.2
2, 200, 220, field2*1.5
3, 300, 350, field1*0.4
];

As an outcome I need the table to have an additional field: 

Tab:
Load * inline [
id, field1, field2, formula, result
1, 100, 150, field1*1.2, 120
2, 200, 220, field2*1.5, 330
3, 300, 350, field1*0.4, 120
];

How do I make the field "formula" be interpreted as an expression? 

I tried using 

1) $(formula) as result

2) $(=formula) as result

3) adding equal sign to the field values (so they look like '=field1*1.2') and using $(formula) again

(and variations of those taken into single brackets) 

but neither seems to work. 

[JordyWegman]

Hi,

Why do you randomly choose field1 or field2 in your formula? Is there an attribute so you know when to take field 1 or 2?

You can solve this with a preceding load:

Tab:
Load:
    *,
    If(id = 1 OR id = 3, field1 * field3,field2 * field3) as result
;
Load * inline [
id, field1, field2, field 3, formula
1, 100, 150, 1.2, field1*1.2
2, 200, 220, 1.5, field2*1.5
3, 300, 350, 0.4, field1*0.4
];

Better would be to know when to take field 1 or 2, that makes it more durable.

Jordy

Climber

[smiling_cheetah]

Hi Jordy,

Thank you for your reply, 

The deal is, I load those 4 fields (id, field1, field2, formula) from an outer source

and while it can be hardcoded (using IF), I would totally want to avoid it 

[tresesco · Accepted solution]

Evaluate() is there for that.

Tab:
Load *,
      Evaluate(field2) as result
 inline [
id, field1, field2, formula
1, 100, 150, field1*1.2
2, 200, 220, field2*1.5
3, 300, 350, field1*0.4
];

Edit : evaluate(formula)

[smiling_cheetah]

Thank you

[JordyWegman]

Hi Tresesco,

The evaluate doesn't work for me, am I doing something wrong?

Jordy

Climber

[kge01]

This solution doesn't work for me either. I think the field for evalute is ment to be "formula" (see script below).

But this doesn't give a valid result.

Qlik seems to not understand the reference to the field-values.

Tab:
Load *,
      Evaluate(formula) as result
 inline [
id, field1, field2, formula
1, 100, 150, field1*1.2
2, 200, 220, field2*1.5
3, 300, 350, field1*0.4
];

[tresesco]

@kge01 , @JordyWegman , you are right; I stand corrected. Inside the inline load we can't refer the field like that, in fact - in such scenario, the field names would be treated as strings rather than the field values. Also, my field reference was wrong for evaluate(), I meant formula. I am correcting now. However, the problem still remains. So to deal with such cases - which I don't find very common - we can follow a method that Jordy suggested or something similar using some row-reference technique. Thanks  

[JordyWegman]

HI @tresesco ,

Thanks for getting back to this! They are indeed not common and I would solve it differently maybe now.

Jordy

Climber

[dirk_fischer]

Hi Tresesco

if I try your propsal with evaluate(formula), I get only Null() as a result. I just copied your code, replaced field2 by formula and ran it.

Any suggestions, why this is not working for me?

Best regards

Dirk