slide with two max rows or lastest rows

[Julio_Moyano]

Hi,

I am trying to make a slide to get the two latest rows for each people.

the table 1 is:

Name	date start	date end
john	01-02-21	30-02-21
peter	05-03-21	17-03-21
cara	01-02-21	30-02-21
peter	19-03-21	20-04-21
cara	02-03-21	15-03-21
john	01-03-21	11-03-21
john	15-03-21	02-04-21
john	03-04-21	07-04-21
peter	23-04-21	30-04-21

I need to get this slide (the two latest date end)

Name	date start	date end
peter	19-03-21	20-04-21
peter	23-04-21	30-04-21
john	15-03-21	02-04-21
john	03-04-21	07-04-21
cara	02-03-21	15-03-21
cara	01-02-21	30-02-21

I have tried to modify this expression, but I could not it

(
[_THISROW]
= MAXROW(
"Table 1",
"date end",
([_THISROW].[Name] = [Name])
)

thanks

[Joseph_Seddik]

Please post this question in "AppSheet Q&A" section, not in "Tips & Tricks". Thank you!

[SkrOYC]

I was thinking about this but it's actually pretty hard. I'm hitting a limit in my AppSheet knowledge 😂

Maybe @Steve could help?

It's not hard to get the last two, but you also want it to dinamically take the two of each [Name].

Solutions that may work would require the uneficient use of VC on the table.

Tell me if you are interested

[Joseph_Seddik]

Julio has kindly created a similar post already in the Q&A section 🙂

[graham_howe]

I knew I'd seen seen this question before 😀 But actually I'm interested so see we went with different approaches. I didn't use a VC or additional table but instead went with expressions only, albeit across two slices.

[Joseph_Seddik]

I really like it @graham_howe . Max it out then go Max the rest, smart solution, it reminds me of playoffs 😀

I'd actually prefer your solution over mine for the absence of VCs and additional tables, also because I like it, much 🙂

@Julio_Moyano better go with this one!

[graham_howe]

I've been thinking about this one and I have an approach which seems to work. First it is important to have a unique identifier as a key in the table, I called this field [ID]. Next create a slice, I call it LatestRecords using the same row expression that you already had:

(
  [_THISROW]
  = MAXROW(
    "Table 1",
    "date end",
    ([_THISROW].[Name] = [Name])
  )
)

 The create a second slice, Latest2Records, which references the first slice in the row formula as follows:

OR(
  IN(
    [_THISROW],
    SELECT(LatestRecords[ID],[_THISROW].[Name] = [Name])
  ),
  (
    [_THISROW]
    = MAXROW(
      "Table 1",
      "date end",
      AND(
        ([_THISROW].[Name] = [Name]),
        NOT(
          IN(
            [ID], 
            SELECT(LatestRecords[ID],[_THISROW].[Name] = [Name])
          )
        )
      )
    )
  )
)

This second slice should contain what you are looking for.

[Julio_Moyano]

Hi @graham_howe , thank you!

Yes, your solution is working very well

The only issue is the appsheet has started to work slower, for instance if I want  to modify and save in UX

[graham_howe · Accepted solution]

I was worried that this might be slow, if you look at the logic it is doing a lot of comparisons across the same dataset and if that gets big it is going to be inefficient. When you tried the VC approach suggested by @Joseph_Seddik was that any faster?

I could imagine another approach where the row condition looks to see if each row is in the top 2 rows for that name, but it might be slow as well:

IN(
  [ID],
  top(
    orderby(
      filter(
        "Table 1",
        ([_THISROW].[Name] = [Name])
      ),
      [date end],
      true
    ),
    2
  )
)

 I have not tested this so don't know if it works.

UPDATE to say this expression would be for a new slice, it would replace both the others.

[Joseph_Seddik]

I don’t think a vc-based solution would be “any faster” than using slices instead 🙂

[Julio_Moyano]

In my case, the solution by @Joseph_Seddik is faster. I had not issue with appsheet platform, the appsheet works like it worked before in speed terms

[graham_howe]

Out of interest, did you try the other slice expression I provided? I would be interested to know if that is slow. It is certainly the simplest of the solutions, but that doesn't mean it's quick.

[Steve]

This is the solution I'd use.

[Julio_Moyano]

Hi @graham_howe  I am testing this expression and it works very well. Thanks!

This expression I am going to use

[desmond_lee]

([_THISROW]=MAXROW("Tool Assign","ASSIGNED TIMESTAMP",AND(([_THISROW].[STANDARD TOOL ID]=[STANDARD TOOL ID]),[ISSUE LIST]=0)))

[desmond_lee]

The first slice really work perfectly. And I even put my name as the latest row and it shows up my name. ([_THISROW]=MAXROW("Tool Assign","ASSIGNED TIMESTAMP",([_THISROW].[STANDARD TOOL ID]=[STANDARD TOOL ID])))

Next slice will be the conditions

[desmond_lee]

So if I am able to achieve to filter the latest rows of the Standard Tool ID how do I nest additional condition like Col S=0, Col T=0, Col V=0 & Col W =0 condition

[Julio_Moyano]

Hi, I created similar post in this link

View solution post