Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Creator

## 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:
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.

Labels (3)

• ### Script

1 Solution

Accepted Solutions
MVP

Evaluate() is there for that.

```Tab:
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)

10 Replies
Partner - Master

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:
*,
If(id = 1 OR id = 3, field1 * field3,field2 * field3) as result
;
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
Creator
Author

Hi Jordy,

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

MVP

Evaluate() is there for that.

```Tab:
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)

Creator
Author

Thank you

Partner - Master

Hi Tresesco,

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

Jordy

Climber

Work smarter, not harder
Contributor II

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

MVP

@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

Partner - Master

HI @tresesco ,

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

Jordy

Climber

Work smarter, not harder
Creator

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

Tags
Community Browser