Read formula and calculate based on fields

[sbfernandes]

Greetings.

In the QlikSense script, I have a table with some fields and a formula descriptor that defines the formula to be used for output. Is there a way to interpret the text in formula as a field and get the appropriate result?

ID	FORMULA	UNIT_RATE	MTOW_TON	FIXED_RATE
1	UNIT_RATE * ROUNDUP((MTOW_TON * 2), 0)	10	50	1000
2	FIXED_CHARGES * 0.25	20	40	2000
3	MAX(UNIT_RATE * (MTOW_TON/1000), 100)	50	1000	5

The expected outvalue value is shown in column EXPECTED_OUTPUT_VALUE below along with the calculation

ID	FORMULA	UNIT_RATE	MTOW_TON	FIXED_RATE	EXPECTED CALCULATION	EXPECTED OUTPUT VALUE
1	UNIT_RATE * ROUNDUP((MTOW_TON * 2), 0)	10	50	1000	10 * ROUNDUP((50 * 2), 0)	1000
2	FIXED_CHARGES * 0.25	20	40	2000	2000 * 0.25	500
3	MAX(UNIT_RATE * (MTOW_TON/1000), 100)	50	1000	5	MAX(50 * (1000/1000), 100)	100

[marcus_sommer]

It's not possible - it remains always a string. By selecting a single one the string could be interpreted into a formula with a $-sign expansion but I assume that's not expedient in your case. The only way to perform row/column-value-depening different calculations is the use of appropriate nested if-loops.

[jpenuliar]

Possible on the front-end, using combination of  functions Pick,Match and variables.

your formula has to be valid as qlik function

[Test]:
Load * Inline [
ID, FORMULA, UNIT_RATE, MTOW_TON, FIXED_RATE
formula1, "UNIT_RATE * ROUND(MTOW_TON * 2)", 10, 50, 1000
formula2, "FIXED_RATE * 0.25", 20, 40, 2000,
formula3, "RANGEMAX(UNIT_RATE * (MTOW_TON/1000), 100)", 50, 1000, 5
];

LET vNROWS = NoOfRows('Test');

FOR i=0 to (vNROWS-1)
LET vVariable_Name = Peek('ID', i, 'Test');
[$(vVariable_Name)] = Peek('FORMULA', i, 'Test');

next

chart expression: Pick(Match(ID,'formula1','formula2','formula3')
, $(formula1)
, $(formula2)
, $(formula3)
)

[marcus_sommer]

Logically it remains an if-loop - it simplifies only the handling.

Be aware that there are reasons for not supporting this kind of measurements natively - mainly the serious disadvantages in regard to the performance. 

[diegozecchini]

Hi!
To dynamically interpret the formula in the FORMULA field and calculate the expected output in Qlik Sense, you can use an Expression Evaluation approach. However, Qlik Sense scripting does not natively evaluate string-based expressions like a formula. Instead, you can preprocess the formula logic outside Qlik or use Qlik's scripting creatively to achieve the desired results.

Qlik Sense scripting does not support direct evaluation of strings as formulas. To achieve this, we parse and map formulas to fields during the script load.

Example Script:

LOAD
ID,
FORMULA,
UNIT_RATE,
MTOW_TON,
FIXED_RATE,
IF(ID = 1, UNIT_RATE * CEIL(MTOW_TON * 2, 1), // Formula for ID 1
IF(ID = 2, FIXED_RATE * 0.25, // Formula for ID 2
IF(ID = 3, MAX(UNIT_RATE * (MTOW_TON / 1000), 100), NULL()))) AS EXPECTED_OUTPUT_VALUE
FROM [YourDataSource];
This method hardcodes the logic for each formula. While effective for small datasets, it’s not scalable.

On other hand you can preprocess data in a more dynamic way using tools like Python or R to evaluate formulas dynamically before loading the results into Qlik Sense.

[sbfernandes · Accepted solution]

Greetings.

Thank you all for your suggestions and feedback. In this case I was able to solve the issue by using the Evaluate() function in script. 

This involves ensuring the functions can be interpreted by QlikSense and field names are present in the table.

Eg: MAX as RangeMax, ROUNDUP as Ceil.

=Evaluate(RANGEMAX(UNIT_RATE * (MTOW_TON/1000), 100))

[diegozecchini]

good idea thanks for sharing