I have a table with many entries for different products; let’s say 100 entries for product X and 90 entries for product Y and 4 entries for product Z. Each entry is time stamped, although I guess Coda does this too.
Can I create a view that shows me the most recent 5 entries for each product (or all entries when there are less than 5 entries)?
yes you can @Gregg_Stebben
Sort the column as you wish and then apply
thisTable.ColumnName.Slice(1,5) in case you want to show the first 5.
I can already see your second question coming, but first try this one. cheers, christiaan
You’re funny…ok, I hope I can deliver on the second question!
I will try this…I did try Slice, but I was using it wrong.
Hey, @Christiaan_Huizer…that works great! What’s the question you thought I would ask you???
@Gregg_Stebben , good to read!
I had two in mind actually, first that you did not notice the
thisTable (to get the complete list) and second you would not be happy with a result as below:
but I am glad it is solved for you!
@Christiaan_Huizer I did notice the “thistable” and it was a first for me, but I am so much of a novice that I didn’t understand it was significant. Layout wise, your solution is perfect for me, thank you again!
I finally got time to mess with this late last night and realized it’s not quite what I am looking for…if I understand correctly, this returns the last 5 values of Column X…my goal is a little different. Using your example, I have multiple entries, each timestamped, for Screw 6 mm (90 entries), Screw 10 mm (50 entries) and Screw 12 mm (4 entries)…my goal is to filter out all entries for all screws except the last 5 or less. So the last five entries for Screw 6 mm, the last five entries for Screw 10 mm and all 4 entries for Screw 12 mm since there are only 4. Would you know any way to do that?
@Gregg_Stebben , so there is next question
I made the scenario below, maybe it provides you some insights. I left the steps in separate columns in the hope it helps to get you up to speed:
First I make a list of the columns:
Second I create a list of all the values in the table and sort it:
thisTable.[TS per screwtype].ListCombine().Sort()
Third, I filter and slice it in max 2 (for my list is rather limited).
thisRow.filter(thisRow.[All TS over all screws].Slice(1,2).Contains(thisRow.[TS per screwtype]))
The result is that the first concerns a screw of 6, the second value one of 10mm, but of course if you have 100+ values, you have a more balanced outcome.
I hope it helps you out, Cheers, christiaan
@Christiaan_Huizer Thank you! I can’t wait to try it!!
Following up with you and @Christiaan_Huizer from Support!
Christian we both loved your solution!
And we were able to launch from there to create a column that we could group into a pivot and see the top five as their own group.
I wanted to share how we can label a row as a “top five” in case it needs to be filtered into a view & you want to see the full top five rows. Essentially bucket rows into top five.
if([ thisTable ].filter(ProductType = thisRow.ProductType).DateCreated.Sort(False()).Slice(1,5).Contains(thisRow.DateCreated), “Recent Five”, “Not Recent”))
From there we could group by this column and see the top five next to not recent.