Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Seier-Alsenz
Contributor II
Contributor II

Calucalte with Symbol from Data

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

Labels (1)
1 Reply
marcus_sommer

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;