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])), https://www.appsheet.com/template/showtable? 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])
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.
Have you set the column as read_only when using the sheet formula?
@Aleksi_Alkio Read only with sheet formulas.
May I ask what is the correct syntax in Excel and what is the format after the convert?
@Suvrutt_Gurjar Was using a real column with app formula, using a VC with the app formula works.
Excel: {=MAX((TableB[ID]=[@ID])*TableB[Date])} Spreadsheet Formula: =ArrayFormula(MAX((TableB[ID]=TableA[[#This Row],[ID]])*TableB[Date]))
User | Count |
---|---|
43 | |
28 | |
24 | |
24 | |
13 |