How to use the result from the last row as input for the next row

AppSheet AppSheet Q&A
,
1

Hi all,

I am trying to create a Bayesian inference app that updates the probability of a hypothesis based on new evidence. The calculation relies on the result of the calculation from the previous row. Here NVC stands for New Virtual Column. However, when I change the code for NVC_P(H) from

IF([NVC_ENumber]=1, 0.5, 0)

to

IF([NVC_ENumber]=1, 0.5, [NVC_Prev_P(H|E)])

it hangs up and times out. Here is a sketch of the “circular” reference that is causing the problem. What is the best way to do this type of thing?

steakdeprived_0-1742595417978.png
steakdeprived_0-1742595417978.png943×305 14.1 KB

Thanks,

Ryan

1 Like
2

The expression depends on the definition of “previous row”. If you are guaranteed that rows are always inserted in the correct chronological or sequential order, then you can use that “ordering” column.

For instance I see you have NVC_ENumber and it looks to be sequential. So you could grab the value using that by decrementing the current rows value by 1. You also need to account for when you ARE on the first row. The resulting expression would be something like:

IF ([NVC_ENumber] = 1,
       "",
       SELECT(_**The Table Name**_[NVC_Prev_P(H|E)], 
                       [NVC_ENumber] = [_THISROW].[NVC_ENumber] -1)
)

Based on your arrows, it looks like you were using a temp column and then copying the value into [NVC_P(H)]?? If so, you can remove the temp column and se tthe expression above as the Initial Value of the [NVC_P(H)] column directly.

I hope this helps!!

3 Likes
3

If you capture things on-the-fly, then when you record the data the records can automatically link to the previous one; all you need is a way to get the “last” id in some way.

Your image looks like it’s from a [Related Whatever] column, which means these records are ref connected to a parent - yes?

If so, you can use a technique to extract out the last item out of that list (on the parent level) - then you can pull this into your child record, when making the new record, to capture the ID of the “previous” child record. When you save the new child record, it’s ID then replaces the “last” id on the parent… resetting the system for the next child record to be made.

I have a video that highlights how to do this:

Hope it helps!

2 Likes
4

Hi everyone, thank you for your replies. I came up with a solution to avoid the circular reference by initializing the final thing I want to calculate, P(H|E), with a placeholder value of -1.0, and then running a bot that does the final calculation after the record has been created.

You all had good advice on how to create the ordering necessary to get the value from the previous record in the inline view, and I wanted to share how I did that here.

The ordering is done with a virtual column, NVC_ENumber

count(
split(
left(
concatenate(Select(evidence_for_hypotheses[evid_id], ([hyp_id]=[_THISROW].[hyp_id]))),
find(
[_THISROW].[evid_id],
concatenate(Select(evidence_for_hypotheses[evid_id], ([hyp_id]=[_THISROW].[hyp_id])))
) + (LEN([_THISROW].[evid_id]) - 1)
),
" , ")
)

The previous value of P(H|E) is put in a virtual column NVC_Prev_P(H|E)

DECIMAL(
INDEX(
SELECT(evidence_for_hypotheses[NVC_P(H|E)],
AND(
[NVC_ENumber]=[_THISROW].[NVC_ENumber]-1,
[hyp_id]=[_THISROW].[hyp_id]
)
), 1)
)

This is used conditionally in the virtual column NVC_P(H)

IF([NVC_ENumber]=1, 0.5, [NVC_Prev_P(H|E)])

And finally the bot updates a column in the evidence_for_hypotheses table called P(H|E), which is the final thing I wanted at the end

[NVC_P(E|H)][NVC_P(H)]/([NVC_P(E|H)][NVC_P(H)]+[NVC_P(E|~H)]*(1-[NVC_P(H)]))

Again, thank you all for looking at this problem with me!

2 Likes
5

@steakdeprived Glad you got a prototype working! :flexed_biceps:

Just FYI, the solution you’re describing would be very heavy on computations - and depending on the size of the tables in question, will really have a negative impact on your app.

Often time, brute force methods like this are a great proof of concept that you can accomplish what you’re wanting; then from there it’s a matter of building out the appropriate infrastructure in your app to more efficiently support achieving the answer.

Happy apping!

1 Like