Ventana software support forum

Hi
I have a look up table read in based on a x,y. I would like to extrapolate from this. When I copied some examples to make them work, it appears that the look up is taking from the nearest value on the lookup table, rather than extrapolating. I know how to set extrapolation for a single value of x using LOOKUP EXTRAPOLATE(). How do I do that with two dimensions? I hope that makes sense.

Equation to change for extrapolation:

sum (
IF THEN ELSE ( gfunct subscript = borehole array gfunct[borehole array size!]
, gfunction lookup[borehole array size!] ( dimensionless time selected )
, 0 )
)

Thank you!

I don't quite follow what you want to do.

Can you upload a simple model that demonstrates?

If I understand, you want a 2D lookup table, with extrapolation at the edges?

I think you'd have to do it with VECTOR LOOKUP in two steps. I'll check whether I have an example.

Hi Tom,

You seemed to suggest there may be an example hanging around. Have you been able to track it down? I will return to this over the next few days.

Ah yes ... turns out there is one, the VECTOR LOOKUP.mdl in the examples that install with Vensim (look in users/public/vensim/models).
https://vensim.com/documentation/fn_vec ... tor+lookup
The example uses mode 0 (regular lookup, no extrapolation) but if you change the mode switch to 3 it should do what you want. A little testing may be in order - if both x and y are out of range, the extrapolation in the corner may be too aggressive.

Yeah, I took a look. Again, I cannot understand the documentation on subscripts. What are the XDim and YDim values supposed to be? I have borehole count on Y. I have dimensionless time on X. Should XDim be equivalent in length to the number of dimensionless times in the table? For example, if I have dimensionless time 1, 2, 3, 4, should I have XDim = x1, x2, x3, x4?

Unrelated-ish to the problem at hand, but why the heck would a person even be required to assign the dimensions if using a lookup table? Can't that be completely programmed out so Vensim simply counts the dimensions? It seems tedious and redundant.

I beat my head on this a little bit. I have nothing to show for it. The VECTOR LOOKUP example does not have 2 dimensions of assigned x, y values for the "lookup values". It simply picks a value good enough between an xmin and xmax, and a ymin and ymax. Really simply here - I want to read in the attached table, choose a value of Y (column headers 1 - 6 or B - G), choose a value of X (rows 2 - 28), then get an interpolated value between x4 (row 5) and x5 (row 6) (or whatever). If the value is outside the table range, then extrapolate from xmax to chosen x (or whatever).

Basic, I think, but I am not sure how to do that in Vensim.

Does the attached do what you need?

It's the only way I can think of to get at the value you want.

The 2D vector lookup definitely has 2 dimensions, but I don't think that's what you want. It sounds like you want to interpolate over time (row) with extrapolation, but not over borehole (column), which is categorical rather than continuous?

If so, admin's solution is correct, albeit a little inefficient because it does the lookup on all 5 response columns. There might be a faster way to do that with VECTOR LOOKUP, but I doubt it's worth the complexity.

If you want extrapolation from the endpoints, rather than interpolation within the time bounds only, you'd have to replace one line:
data from Excel f[SUB!]( time to get)
-> LOOKUP EXTRAPOLATE( data from Excel f[SUB!], time to get)

Hi all,

Thank you for taking a look at this. I think this is looking good. It looks like admin is taking in column 1 = y = Excel Column B, then assigning a dimensionless time from column A/0. The result is meant to interpolate between rows, I think.

If that is the intent, which it is for me, the .mdl that admin sent is seemingly correct for dimensionless time 0. Great success.

Can I ask, why is the sum() function always wrapping these IF THEN ELSE statements for subscript retrieval? I have been doing this because I am copying similar work others have here, or have shown me, but I do not understand what that effectively does.

Also, can someone please point out in the manual where these special characters are for Vensim equations? This input in the IF THEN ELSE statement is not something I understand entirely, shown below. It seems to be a conditional within the conditional, but I do not know the exact meaning of the exclamation point or the square brackets versus parentheses, for example.
data from Excel f[SUB!]( time to get)

Finally, @Admin could you please let me know the rationale for the min and max values of "time to get"? Are those arbitrary? If so, does the "increment" represent the nearest interpolatable (if that is word) decimal, rounded from the value field? In this case, the increment is shown as .25 and min is -10, max is 10.

Thanks again!

tomfid wrote: ↑Mon Dec 09, 2024 4:12 pm If so, admin's solution is correct, albeit a little inefficient because it does the lookup on all 5 response columns. There might be a faster way to do that with VECTOR LOOKUP, but I doubt it's worth the complexity.

If you want extrapolation from the endpoints, rather than interpolation within the time bounds only, you'd have to replace one line:
data from Excel f[SUB!]( time to get)
-> LOOKUP EXTRAPOLATE( data from Excel f[SUB!], time to get)

Good info here Tom, thank you. I just need to clear up some of my lack of understanding around characters, discussed above.

Hey Tom, I did a bit of review on this. It appears that I get the error "-data from Excel f- is a special type and cannot be used as a normal variable, when I try implementing the LOOKUP EXTRAPOLATE() within the conditional IF THEN ELSE(). I tried resolving this another way, by first reading in sum(data from Excel f[SUB!]) as a unique variable, but the error message is the same.

Sorry for the delay, I've been away.

geo_curious wrote: ↑Sat Dec 14, 2024 11:18 pmCan I ask, why is the sum() function always wrapping these IF THEN ELSE statements for subscript retrieval? I have been doing this because I am copying similar work others have here, or have shown me, but I do not understand what that effectively does.

It's effectively a "loop" in programming terms. There are no for/next loops in Vensim, to get around that, you can use "sum" to loop over every element in a range. We should really think of an alternative way to do this that is easier to understand.

geo_curious wrote: ↑Sat Dec 14, 2024 11:18 pmAlso, can someone please point out in the manual where these special characters are for Vensim equations? This input in the IF THEN ELSE statement is not something I understand entirely, shown below. It seems to be a conditional within the conditional, but I do not know the exact meaning of the exclamation point or the square brackets versus parentheses, for example.
data from Excel f[SUB!]( time to get)

The exclamation mark tells Vensim which subscript range(s) to sum over. "data from Excel f" is a lookup, and you always pass a value to a lookup (in this case "time to get").

https://www.vensim.com/documentation/fn_sum.html?q=sum

geo_curious wrote: ↑Sat Dec 14, 2024 11:18 pmFinally, @Admin could you please let me know the rationale for the min and max values of "time to get"? If so, does the "increment" represent the nearest interpolatable.

I chose the min/max based on the values in the "ln(t/ts)" column of your spreadsheet. The min/max/increment are only used during SyntheSim to set the slider up, they are not used for any other purpose.

geo_curious wrote: ↑Sun Dec 15, 2024 12:08 am Hey Tom, I did a bit of review on this. It appears that I get the error "-data from Excel f- is a special type and cannot be used as a normal variable, when I try implementing the LOOKUP EXTRAPOLATE() within the conditional IF THEN ELSE(). I tried resolving this another way, by first reading in sum(data from Excel f[SUB!]) as a unique variable, but the error message is the same.

Can you upload what you are doing, we can then take a look.

I will upload here when I get a moment. Working by weekday. Vensim by weekend.

Administrator wrote: ↑Mon Dec 16, 2024 2:13 pm Sorry for the delay, I've been away.

geo_curious wrote: ↑Sat Dec 14, 2024 11:18 pmCan I ask, why is the sum() function always wrapping these IF THEN ELSE statements for subscript retrieval? I have been doing this because I am copying similar work others have here, or have shown me, but I do not understand what that effectively does.

It's effectively a "loop" in programming terms. There are no for/next loops in Vensim, to get around that, you can use "sum" to loop over every element in a range. We should really think of an alternative way to do this that is easier to understand.

geo_curious wrote: ↑Sat Dec 14, 2024 11:18 pmAlso, can someone please point out in the manual where these special characters are for Vensim equations? This input in the IF THEN ELSE statement is not something I understand entirely, shown below. It seems to be a conditional within the conditional, but I do not know the exact meaning of the exclamation point or the square brackets versus parentheses, for example.
data from Excel f[SUB!]( time to get)

The exclamation mark tells Vensim which subscript range(s) to sum over. "data from Excel f" is a lookup, and you always pass a value to a lookup (in this case "time to get").

https://www.vensim.com/documentation/fn_sum.html?q=sum

geo_curious wrote: ↑Sat Dec 14, 2024 11:18 pmFinally, @Admin could you please let me know the rationale for the min and max values of "time to get"? If so, does the "increment" represent the nearest interpolatable.

I chose the min/max based on the values in the "ln(t/ts)" column of your spreadsheet. The min/max/increment are only used during SyntheSim to set the slider up, they are not used for any other purpose.

Good explanation here. Thank you!

I have a correction. Here is a small version of the model. The .xlsx is shown earlier in the thread. You can see the value from table data is not being extrapolated.

This isn't extrapolating because it isn't using LOOKUP EXTRAPOLATE. gfunction lookup from Excel[borehole array size!](time to get) needs to be LOOKUP EXTRAPOLATE( gfunction[size!], time to get )

For clarification, the model above will not work if LOOKUP EXTRAPOLATE() is implemented.
In the gfunct subscript equation, you can input LOOKUP EXTRAPOLATE( gfunction lookup from Excel[borehole array size!],(time to get)) to find the error: "is a special type and cannot be used as a normal variable."

Just a reminder that the LOOKUP EXTRAPOLATE() does not work on these subscripts. If anyone has thoughts on a solution, please let me know.

You can fix that by extracting the inner lookup from the outer sum, as in the attached version.

Thanks Tom. This is working for me now.

.

Here is my slight revision of the model to avoid subscript propagation and analyze the correct output .