topic Re: Read formula and calculate based on fields in App Development

Read formula and calculate based on fields

sbfernandes — Mon, 09 Dec 2024 08:58:35 GMT

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

Re: Read formula and calculate based on fields

marcus_sommer — Mon, 09 Dec 2024 12:34:44 GMT

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.

Re: Read formula and calculate based on fields

jpenuliar — Thu, 12 Dec 2024 18:02:47 GMT

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');

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

Re: Read formula and calculate based on fields

marcus_sommer — Fri, 13 Dec 2024 06:02:54 GMT

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.

Re: Read formula and calculate based on fields

diegozecchini — Fri, 13 Dec 2024 09:42:43 GMT

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.

Re: Read formula and calculate based on fields

sbfernandes — Sun, 15 Dec 2024 04:23:30 GMT

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))

Re: Read formula and calculate based on fields

diegozecchini — Mon, 16 Dec 2024 07:27:16 GMT

good idea thanks for sharing