How to calculate by referring to other columns written textually in a column in spotfire?

[Kibong Kang]

Column A has a Formula String like '100-'.

Is there a way to calculate the actual value of column ?

The value of column A can actually be Formulas for calculation like '100-[C]' or '50-'.

[Jose Leviaguirre]

Hello Kibong Kang

Could you please elaborate more about this interesting use case? I guess you are trying to evaluate the custom expression defined in a column, so I created a small dataset to test with

A,B1,[A]2,[A]*23,[A]*[A]

I suppose you want the output to be [D], Column [C] is for another approach, so ignore it for now.

Approach A

Maybe the simplest approach is to create a calculated columns for each calculation case. For example:

case when "[A]" then [A] when "[A]*2" then [A]*2 when "[A]*[A]" then [A]*[A]end as [D]

I you don't have many expressions, you could index them by replacing each case with a number or letter.

Approach B

You can see the column [C] to update when I mark the corresponding row. This was done by creating a pass through data function that takes the first marking element from column and outputs the result to a document property called dp. The passthrough data function script is just "x" as well as the input and output parameters. The script parameters are an Expression for the input parameter, and a document property called dp as the output parameter. Make sure the refresh function automatically option is checked. You can also define the R script as "y<-x" to be more clear and set the x and y as the input and output parameters, but a simple x works just fine. The expression for the input parameter is limited by marking defined as first([Data Table].)

 

Now you can use this document property on the calculated column [C]

${dp} as [C]

This is not giving the output you want because you have to click each expression defined on each row for column , but it is a start. I am sure you can give it more thought so instead of getting one value at a time through the transfer data function, you could have the output be a unique concatenation of values to a string that can later be parsed to calculate each input for each row. This might additional steps or even IronPython triggered when the dp document property changes. This can work for small datasets.

Please share more details about your use case.

[Kibong Kang]

Thank you.

I can't specify the information about the calculation formula for 'Approach A'.

The 'calculation expression' value is the value of the data that I do not have access to, and finds the target value by multiplying or adding a quadratic operation and another column value or integer value.

If you modify 'Approach B' well, you can get the value you want, but each row has a different value, so it should be calculated as an individual row.