How do I get the last record from a filtered table

Ask for Help
1

I have a summary table of TRADES filtered by Date (top table)

I need to get the CumResult from the last record of the filtered trades records (bottom table). Record 5 in this example.

The records in the TRADES table are sorted by Date then by Time

I can’t use min or max since the value can be positive or negative. And it can go from positive to negative. I just want to grab the value from the last record.

Seems easy because I can see it :slight_smile: … but I’m not sure how to get it

image
image1676×1196 194 KB

a seprate but related question, how do I sort by 2 fields in this statement?

rows = Trades.lookupRecords(Date=$Date, sort_by = 'DateTime')

I have separate date and time fields but didn’t know how to sort by 2 fields, so I combined them. It works, but isn’t necessary if I can sort first by date, then by time

thanks

2

found the answer to getting the value of CumResult for the last record… stole from a couple different posts :wink:

cumResult = list(Trades.lookupRecords(Date = $Date))

return cumResult[-1].CumResult if cumResult else None

still need help on double sort

3

I think you could replace first line by following:

curResult = sorted(Trades.lookupRecords(Date=$Date), key=lambda r: (r.Date, r.Time))

Caveat nevertheless: performance-wise, it’s certainly much less optimized than a sort_by with a single column. On (very) big tables, this difference could matter.

4

thank you. I.ll stick to my combined time/Date field and a single sort_by since it’s working

5

oops. My formula to get the last record works most of the time, but not always

I am using this formula in the AdjR field of the TRADES [by Date] table

cumResult = list(Trades.lookupRecords(Date = $Date))

x = cumResult[-1].AdjResult if cumResult else None

return x

I stole it from some other posts so am not sure exactly what it is doing, but it’s doing it wrong

The formula is “usually” correct, but not always

image
image1243×808 108 KB

any idea what I did wrong?

6

my bad… Again

I forgot to sort in the lookup equation so that even if records were sorted in my sub table, they weren’t necessarily sorted the same way in the function.

cumResult = list(Trades.lookupRecords(Date = $Date, sort_by="DateTime"))

x = cumResult[-1].AdjResult if cumResult else None

return x
7

You should use lookupOne in that case, easier and faster. Moreover, if this is really the whole formula, the x variable and the return are useless. So you could write your formula as follows:

Trades.lookupOne(Date=$Date, sort_by="-DateTime")

Reference here.

8

thanks jperon
I added .AdjResult to the end to get the value instead of the id and that worked.

Trades.lookupOne(Date=$Date, sort_by="-DateTime").AdjResult

the x and return are part of my debugging process. Makes it easier to try stuff until I get it right :slight_smile: