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.
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)
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
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
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)
Thank you
Hi Tresesco,
The evaluate doesn't work for me, am I doing something wrong?
Jordy
Climber
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 ];
@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
HI @tresesco ,
Thanks for getting back to this! They are indeed not common and I would solve it differently maybe now.
Jordy
Climber
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