Max Array Match/Select Trying to grab a colu...

(Kyle Grieb) #1

Max Array Match/Select

Trying to grab a column value from a matching row using tableA to tableB with a max array match. AppSheet reads the formula from the column and parses it correctly but without valid returns. The return is not maxed and matching rows are ignored (I have a row difference in TableA & TableB). When trying to replace the spreadsheet formula with an AppSheet formula (MAX(SELECT(TableB[Date],[ID]=[_THISROW].[ID])), returns correct results but the app view does not.

If the spreadsheet formula is converted correctly with invalid returns and a conversion into an AppSheet formula produces correct returns on testing but not in a view, what other options do I have to produce the results I’m looking for?

(Works in Excel, not in Appsheet View/Template) TableA: ID,Value TableB: ID,Date TableA[Value]={MAX((TableB[ID]=[@ID])*TableB[Date])} => ArrayFormula(MAX((TableB[ID]=TableA[[#This Row],[ID]])*TableB[Date]))

(Works in Appsheet Template, not in View) TableA[Value]=MAX(SELECT(TableB[Date],[ID]=[_THISROW].[ID])

(Suvrutt Gurjar) #2

Hi @Buglouse,Based on my understanding of description you have given, are you using a real column in table A to compute the expression?

If so, I request you to once try the same AppSheet formula that works in template by creating a virtual column in table A and using the virtual column in the view.

(Aleksi Alkio) #3

Have you set the column as read_only when using the sheet formula?

(Kyle Grieb) #4

@Aleksi_Alkio Read only with sheet formulas.

(Aleksi Alkio) #5

May I ask what is the correct syntax in Excel and what is the format after the convert?

(Kyle Grieb) #6

@Suvrutt_Gurjar Was using a real column with app formula, using a VC with the app formula works.

(Kyle Grieb) #7

Excel: {=MAX((TableB[ID]=[@ID])*TableB[Date])} Spreadsheet Formula: =ArrayFormula(MAX((TableB[ID]=TableA[[#This Row],[ID]])*TableB[Date]))