Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;