Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

smiling_cheetah
Contributor

Interpret field values as formulas

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. 

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Interpret field values as formulas

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
];

 

 

5 Replies
Partner
Partner

Re: Interpret field values as formulas

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

Work smarter, not harder
smiling_cheetah
Contributor

Re: Interpret field values as formulas

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 

Highlighted
MVP
MVP

Re: Interpret field values as formulas

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
];

 

 

smiling_cheetah
Contributor

Re: Interpret field values as formulas

Thank you

Partner
Partner

Re: Interpret field values as formulas

Hi Tresesco,

The evaluate doesn't work for me, am I doing something wrong?2019-03-22 15_38_34-Qlik Sense Desktop.png

Jordy

Climber

Work smarter, not harder