Calucalte with Symbol from Data

Seier-Alsenz — Fri, 05 Jul 2024 10:04:15 GMT

Hello,

I have a data table with the mathematical arithmetic symbol, which I call a "formula" (Table A). Table B contains the values.

Is it possible to calculate them in the script? If so, what would be the solution?

Table A

no	line	op	line2_id
1	100	+	1000
2	100	+	1001
3	100	=
4	200	+	4000
5	200	-	4524
6	200	=

Table B

line2_id	value
1000	500,00 €
1001	1.050,00 €
4000	2.000,00 €
4524	1.000,00 €

I want to calculate table A according to field "Line" and use the operators from field "op".

The result for line "100" should be €1,550 and for line 200 = €1,000.

Kind regards

Re: Calucalte with Symbol from Data

marcus_sommer — Fri, 05 Jul 2024 12:01:46 GMT

In general it should be possible but depending on your real scenario it might become quite ugly. This would be the case if there are many different rows and operations (not only + & - else also * & / ) per line which would require an appropriate ordering with brackets.

In regard to your example the table B could be loaded as mapping table and per applymap() transferred into table A and then the mapped values and the operator are string-concatenated on the row- and column-level and wrapped with an evaluate() - maybe like:

m: mapping load * from TableB;

t: load line2_id, evaluate(concat(applymap('m', line2_id) & op, ' + ')) as Result
from TableA group by line2_id;

The above won't work at once else it hints just for possibilities and it may need several intermediate steps and if-loops to prepare + order + clean the calculation-strings to produce valide statements.

If all op are only + & - you won't need such complex approach else you could simply aggregate the results, for example with something:

m: mapping load * from TableB;

t1: load line2_id, applymap('m', line2_id) as Value, if(op = '+', 1, -1) as Direction
from TableA;

t2: load line2_id, sum(Value * Direction) as Result
resident t1 group by line2_id;

topic Re: Calucalte with Symbol from Data in App Development

Calucalte with Symbol from Data

Re: Calucalte with Symbol from Data