Max value from Column B for each distinct value from Column A

[dbaum]

Does anyone have a technique for accomplishing anything even approximating the type of function available in other platforms that is often known as "for each"? I need to determine the max value of one column that is associated with each distinct value in another column.

I think the following is the expression to evaluate whether the [Last Access] date for a given email was after the email was last added to any [Tenant ID] and before it was last removed (i.e., the log's [Added] column is false) from any [Tenant ID]. What I really need to evaluate is whether an email's [Last Access] date falls between its added and removed dates for each [Tenant ID] to which it has ever been added.

All that occurs to me is to SELECT the list of [Tenant ID] values associated with the email and evaluate for each using the INDEX function. In this case, I'd have to decide in advance the likely maximum number of [Tenant ID] values that any one email will be associated to so that I can explicitly make that many passes with INDEX--i.e., for the value in position 1, then position 2, then position 3, ....

Better approaches are obviously welcome.

AND(
  IF(
  	ISBLANK(
    	SELECT(
      	User Account Log[Log ID], 
        [Email] = [_THISROW].[User Email]
        )
      ), 
    false, 
	  [Last Access] >= 
    MAX(
    	SELECT(
      	User Account Log[Created], 
        AND(
        	[Email] = [_THISROW].[User Email], 
          [Added]
          )
        )
      )
    ), 
  IF(
  	ISBLANK(
    	SELECT(
      	User Account Log[Log ID], 
        AND(
        	[Email] = [_THISROW].[User Email], 
          NOT([Added])
          )
        )
      ), 
    true, 
    [Last Access] < 
    MAX(
    	SELECT(
      	User Account Log[Created], 
        AND(
        	[Email] = [_THISROW].[User Email], 
          NOT([Added])
          )
        )
      )
    )
  )

[TeeSee1]

Hi @dbaum 

I don't think I fully understand your requirements but here is just an idea.

Could you create a check table like this?

Table Access Check

• id: generic id
• user email: ref
• tenant id: ref
• check: vc to check if [last access] is legit for the tenant of this row

With this info, you can make a comprehensive audit report...

[1minManager]

I think I understand what your issue is and I've had similar issues before.  So with mine it was to find the shotest distance between Location A and all the other locations in the same table.  Which itself is easy, but then later I might want to know the shortest distance from every location to location B.  Appsheet ain't really good at this kinda thing as really you need code that can do loops.

So what I did is as follows

• Create an action so that you can flag where every other location show measure to.  So have it write something like [Flag]="ThisLocation"
• Next create a virtual column [Distance] in the location table with a formula like Distance(Any(Select(Table[Location],[Flag]="ThisLocation")),[_ThisRow].[Location])  This calculates the distance from that location to any flagged location
• Next use another virtual column [Shortest] to find the shortest Min(Select(Table[Distance],[Flag]<>"ThisLocation"))  The later is important otherwise it would be measuring the distance to itself

So the sequence is

1. You click the action button
2. [Distance] updates
3. [Shortest] then picks the lowest number
4. Then at somepoint you'll need to clear [Flag]

Hope this helps, sorry I can't really understand you data structure mentioned above.  But hopefully this gives you a pointer on what to do.

There maybe another more elegant way, if there is I'm sure someone will mention it 🤣

Simon@1minManager.com

[Steve]

Please restate your goal using plain language, without any technical jargon.

[SkrOYC]

The solution for the problem explained in the title is not that hard, but the post really confused me.

You said that you need the highes value from a Column B on a certain table for each unique Column A value on the same table. How does that relate to the other details that you explained?

Try telling us the table name and the exact name of Column A and B, the rest of the explanation is confusing IMO

[Joseph_Seddik · Accepted solution]

---

@dbaum wrote:

 I need to determine the max value of one column that is associated with each distinct value in another column.

Create a slice with the following row filter condition:

[one column] = MAX(
  SELECT(table[one column], [another column] = [_ThisRow].[another column])
)

[dbaum]

As always, thanks all very much for wading through this dense scenario and offering guidance. I greatly appreciate your thoughtfulness. Your many questions crystallized for me that this was more muddled in my own head than I even realized and that came through in my attempt to articulate my question and what I was trying to accomplish. Now, I think I've got it sorted out. All your replies helped me think this through and get to a solution. Following is the summary.

Here's the question I was trying to create an expression to answer: When a user last accessed the app, which tenants' data did the user have access to at that time?

Table 1: I have an app with a User Account Log table. Each row details the addition or removal of a user account from one of the app's tenants, including the created date of the log entry. Each user account email can be added or removed multiple times for each of multiple tenants.

Table 2: We all have access to our AppSheet account's "User Activity". While it's not possible to systematically reference account User Activity (at least not currently), the information can be downloaded in a .csv file, which of course can then populate a data source for a User Access Log table within an app. The downloaded file provides all the information that's displayed in the account UI, including the user email, as well as additional detail, such as the timestamp for each user's last access.

So, my need is to evaluate for each entry in the User Access Log whether its Last Access timestamp is--for each Tenant ID in the User Account Log to which the entry is associated (as determined by matching User Email from the User Access Log to Email from the User Account Log)--preceded by a User Account Log entry that Added the user's app account to the Tenant ID without any intervening action that removed (i.e., NOT(Added)) the user's app account from the Tenant ID.

I directly leveraged @Joseph_Seddik's elegant expression pattern. Another critical piece was to leverage the recursive back-reference technique explained elsewhere by @Steve. Here's where I landed.

SELECT(
  User Account Log[Tenant ID], 
  INDEX(
  	SELECT(
    	User Account Log[Added], 
      [Created] = MAX(
      	SELECT(
        	User Account Log[Created], 
          AND(
          	[Email] = [_THISROW].[User Email], 
            [Created] <= [_THISROW].[Last Access], 
            [Tenant ID] = [_ThisRow-2].[Tenant ID]
            )
          )
        )
    	), 
    1
    ), 
  true
  )

[Ed_Cottrell]

post-posting update: some rough edits to the Slice 2 filter expression below as realised I’d left out some bits.  Both expressions fairly vague/pseudo in any case just to get the concept down. 

Hi @dbaum 

As @Joseph_Seddik ’s expression is marked as the solution to your original query, I assume that it is indeed covering your scenario?  The expression offered is clear, simple, and powerful (thanks AppSheet!).

However you mentioned the need to effectively create an equivalent “for each” or  ‘looped expression’ and then mentioned the option to fix the functionality to consider a “likely maximum number of [Tenant ID] values”….. So, if it helps to release some further solutions from your subconscious (they are already there! ?) , I have had the scenario where I’ve needed a “for each” equivalent in AppSheet and have achieved that by using two slices; essentially a slice of a slice:

Table A Slice 1: {Filter expression that gets your ‘groups’ to consider} (a bit like your outer loop or “for each” statement)

Table A Slice 2: IN([ID], Filter(“Slice 1”, [ID] = Maxrow(“Slice 1”, “Some Column”, {some other filter conditions})) which then gets your desired result for each of your ‘groups’ in Slice 1.

Now, I found it possible to combine all into one slice formula that makes use of [_THISROW-n] derefs but also found it to be easier and clearer to split into two slices – and in many cases both slices would likely be useful in an app anyway.

Just spotted your topic and thought I’d share if it helps.

[SkrOYC]

One thing that I learned from @Steve (aha! no one saw that comming just joking, Steve knows it all) is exactly what's asked at the title of the OP.

I could explain it but he already gave it on another post with the basic structure:

Solved: Re: Filtering only last unique rows in a column an... - Google Cloud Community

And I use it a lot on templates, where I need to generate multiple Start:, each one inside the other, to create categories, subcategories and more. The basic structure is like this:

Solved: Re: How to separate rows of a table based on uniqu... - Google Cloud Community

Hope it helps