# Get last n rows from a linked table

So I have a parent ->> child one-to-many relationship.

I want to get data from the last n rows of the child table for any row in the parent (specifically I want to get the mean of the last n rows).

Help?

Hi Craig - How do you define ālast nā?

A particular sort order? The table order? By creation date and time?

P

Good question - the last n defined by a date column.

Child table has two columns - Date and Value. Both are entered manually.

Hi Craig,

It is vugly, but I have an answer for you here.
Last n numbers in a table Ā· Rambling Pete's Quick examples.

Lots of room for kaizen, but it at least calculates the correct answer at the moment.

Regards
Piet

Thank you! I think I understand what you are doing. Slice() is pretty interesting function.

Okay - I modified this a bit bc I donāt use created date and multiple rows with different parents could have the same date. I made the Rank column a combo of the Parent Name and the Rank (thisRow.Metric.Name+ā-ā+rank(thisRow.Date,thisTable.Date ).

Next question - it seems this gets the Earliest 5 rows, not the latest. What do I change for the latest?

Figured that out - added Ascending = True to the Rank column formula

1 Like

I am certain there are a few improvements that the more experienced guys could make, I particularly dislike the fact that I have hard-coded five addRows() to populate the temp storage tableā¦

I despaired of getting this done at one stage yesterday, but got it done in the end and learned quite a bit.

I donāt see where AddRow adds the Value column, yet somehow it does?

runactions(
[Temp storage],
[Temp storage].Name,Nth(thisRow.[Earliest 5 by Parent], 1),/*Endf Nth */
[Temp storage],
[Temp storage].Name,Nth(thisRow.[Earliest 5 by Parent], 2),/*Endf Nth */
[Temp storage],
[Temp storage].Name,Nth(thisRow.[Earliest 5 by Parent], 3),/*Endf Nth */
[Temp storage],
[Temp storage].Name,Nth(thisRow.[Earliest 5 by Parent], 4), /*Endf Nth */
[Temp storage],
[Temp storage].Name,Nth(thisRow.[Earliest 5 by Parent], 5),/*Endf Nth */
) /endrunactions/

It is one of the things that I could not figure out how to do.

So once the above is done, there is a formula in the value column that filters out the value.

I am absolutely certain that it is possible to skip this step, and directly populate the Temp Storage table. I just ran out of steam.

This eliminates the need for the formula on the Value column in Temp storage.

Next is t figure out how to use Slice and ForEach togetherā¦

Thank you. It appears that AddRow uses the display column from Last n Numbers for the value - any way to specify the column? Iāve tried using a ā.valueā column name but it doesnāt workā¦

Figured that out! I had to put the .value after the filterā¦

Combining FormulaMap with RunActions.

This works (albeit always uses the 1st value)
[Temp Storage],
[Temp Storage].Value,
thisRow.[Metrics Tracking].Filter(Rank=nth(thisRow.[Last 5 Metrics Tracking], 1 )).Value,
)

But this doesnāt (I try to use CurrentValue of the list):
[Temp Storage],
[Temp Storage].Value,
thisRow.[Metrics Tracking].Filter(Rank=nth(thisRow.[Last 5 Metrics Tracking], CurrentValue)).Value,
)

Coda keeps saying CurrentValue can match a current value, or a row from a table.

Not sure how to reference the CurrentValue of the listā¦

Figured it out, kinda wonkyā¦

I created a control and then update and reference that control:

RunActions(
SetControlValue(Seq,0),
List(1,2,3).ForEach(RunActions(
SetControlValue(Seq,Seq+1),
[Temp Storage],[Temp Storage].Value,
thisRow.[Metrics Tracking].Filter(Rank=Nth(thisRow.[Last 5 Metrics Tracking],Seq)).Value ,
)))

1 Like

Thanks to this article by Christiaan Huizer, I was able to figure out how to do this without a temp table.

Happy to share if anyone wants.

3 Likes

Please share, it would be a nice conclusion.

P

Here is how I did itā¦

Parent table is called Metrics. Child table is called Metrics Tracking.

In Metrics Tracking I added 3 columns:

1. Metric Name: formula - thisRow.Metric.Name
2. Metric Rank: formula - rank(thisRow.Date,thisTable.Date)
3. last n by Metric: formula - Filter(thisTable, Metric=thisRow.Metric)
.[Metric Rank].Sort(true()).Slice(1, [Sample Size]).ReverseList()

This last column creates a list of the last n rows ordered by date.

I have a control called Sample Size where I can change the sample size dynamically.

On the parent Metrics table, I added these columns:

1. Last n Metrics Tracking: formula - thisRow.[Metrics Tracking].Filter(Metric.Name=thisRow.Name).[Last n by Metric].First()

This formula copies the ordered list from Metrics Tracking.Last n by Metric to the Metrics table

1. Last n Values: formula - thisRow.[Metrics Tracking].Filter(And(Metric.Contains(thisRow),In([Metric Rank],thisRow.[Last n Metrics Tracking]))).Sort(True(),[Metrics Tracking].Date).Value

This formula copies the values from the Metrics Tracking table, based on the previous column.

1. MR Values: formula - Sequence(1,thisRow.[Last n Values].Count()-1).FormulaMap(abs(thisRow.[Last n Values].Nth(CurrentValue+1) - thisRow.[Last n Values].Nth(CurrentValue)))

This formula creates the moving range between each value in the above column (value 1 - value 2, value 2 - value 3,ā¦)

Now that we have the MR values, we can calculate the average moving range, or anything else.

Here is the link to the doc.

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.