Self referential calculated column

[Anthony Quarm 5]

In the past I have run into cyclical logic errors due to two columns being dependent on each other. I have come to understand that Spotfire does not calculate things like ma spreadsheet does. Rather than calculate each variable row by row, it calculates each column before moving to the next one.

So, this got me wondering if I can use the previously calculated values of a column within the same column's calculations.

eg:

X(row1)=5

X(row2)=X(row1)+5

X(row3)=X(row2)+5

X(row4)=X(row3)+5

Final Outputs

X(row1)=5

X(row2)=10

X(row3)=15

X(row4)=20

Is this possible If it is calculating from the top down, I don't see any logic errors here. If it is possible, what would the syntax be

Thank you.

[Fabian Duerr]

Please see this example:

 

https://community.spotfire.com/questions/how-use-value-n-th-row-calculated-column-within-calculated-column-itself-row-n1

[Anthony Quarm 5]

Thank you for taking time to respond. However this is the same link you have provided to me on a previous question. And I still don't understand Data Functions well enough to use them. When I asked about the syntax for doing my calculations in a Data Function, your suggestion was to go learn Python. While I understand the value in learning Python, it is not a feasible solution for this one problem. 

Hence why I was asking if it was possible to do this using the calculated column. I've read about FirstLastNode. And FirstPreviousPeriod. I had hoped there might be a solution with this, however I can not find an example that is similar enough to what i am doing to make any use of it.

If I do got he Data Function route, I suppose the firts setp is just getting a data function that can read my inputs and create an output. If I could get that far, then maybe I could play with the equation in the data function enough to figure out how to get it to work. But no resource on Data fucntions that I have found even expalins the very basics, like how to link it to the input table. Typing in my input table name does nothing for me, and it won't even let me save the data function. Every resource seems to assume the person using it has alreayd done this before. There is absolutely nothing designed to help a new person tackle this, and so far every solution has been "go learn something else". 

I really do appreciate the fact that you keep taking time to respond to my questions. Unfortunately the links you are providing me simply aren't detailed enough for me to createexecute anything of value base don my current knowledge of spotfire.

[Fabian Duerr]

Sorry for double posting the previous link. :(

 

I totally understand that you feel lost with data function. Because I just felt the same when I started. I can record another short video for you with a data function that takes a table and some user input and then returns a new column based on the inputs.

[Anthony Quarm 5]

That is kind of you. I seem to have trouble taking other people's examples and making them work with the data I have been provided. For example, you previously send me a data function dxp file to show me how they work. In it, your input parameters come from a simply name "data.table"

 

My data inputs parameters live on a linked excel spreadsheet. For the life of me, I can't seem to get it to link properly, and I don't know what the syntax is to do this. The spreadsheet is called EurSheet in my dxp, but typing that alone into the input parameters does not appear to work.

[Fabian Duerr]

So here's a little tutorial that I made from scratch. I'm not an expert and what you see there might not be best-known method, but this is how I currently work ;)

 

Don't worry too much about the names of your variables in your function. It can be 'data.table' or 'DaTa.TaBlE' or whatever. But make sure that you pass the right argument with the input handler

 

/modules/file/icons/video-x-generic.png datafunction.mp4

 

PS: The error in your case probably comes from the fact that when you use EurSheet$OilCalcQI you use the entire column. So you try to evaluate a boolean expression with more than just a single value (single row). Check out my tutorial to see how to loop thru each row.

 

Let me know your questions. 

[Anthony Quarm 5]

I have come up with this based on a prior example you sent me. The EurSheet input parameters are defined and I've tested them with simple calls to ensure they can read data. I've also tested a simple output to ensure I can write to th table. Now I have moved on to trying to actually implement my ifelse statement.

As I understand the code it is first using nrow to find the total number of rows in EurSheet and assigning that value to n

It then replaces whatever values may exist in OilRateFunctionCalc with a zero, for however many rows were defined in n

It then writes the first value of OilCalcQi (which is a constant anyways) to the first row of OilRateFunctionCalc

It not runs a loop, based on the number of rows defined in n, and I believe the 2 there tells it to start at 2 instead of 1.

Within this loop is an if statement. If the expression after "if" is true, it should assign the value of the second expression to OilRateFunctionCalc. And if false, it should assign the second expression.

 

Assuming I understand this much of the logic. The error from Spotfire is:

TIBCO Enterprise Runtime for R returned an errorThe data function 'OilRateFunction' could not be executed.Error in withCallingHandlers({

    n <- nrow(EurSheet)

    OilRateFun : replacement has length zero

    eval(script, envir = .GlobalEnv)

    eval(script, envir = .GlobalEnv)

    withCallingHandlers({This seems to suggest that it is not returning the number of rows in EurSheet properly, which in turn would throw everything else off. Am I semi correct in my thinking here And if so, can you assist me in figuring out where I have gone wrong  Thanks.

[Anthony Quarm 5]

Thank you for the tutorial. I have gotten the part where I read data in to work. I just can't seem to get it to do what I need it to do with the data. I've written some more detail above as to what is happening.

[Fabian Duerr]

Make sure to go thru every single row. Don't pass an entire column to your loop ;)

 

EurSheet$OilCalcQI (entire column!) has to be replaced with EurSheet$OilCalcQI (<- value from row 'i' in this column). Do this for all other columns as well

[Anthony Quarm 5]

I just wanted to let you know I was able to get it working. Thank you very much for your guidance and assistance.

[Fabian Duerr]

Perfect! Congratulations to your first (but hopefully not last) data function! :)

[Anthony Quarm 5]

Thanks. I've already written 3 others.