Re: FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), R... - Page 3

Steve · 05-11-2020 10:20 AM

Core concepts & functions

Adding & updating rows

Excel alternatives

Last row of the spreadsheet

Rows of this thing only

Miscellaneous

Grant_Stead

What’s the point of “after” values? I’ve never understood why I would use that?

Fabian_Weller

@Steve Could we now do double dereference?

In this article we read that this is not possible. (under " Double Dereference Expressions")

Let’s say we have 3 Tables:
Parent
Child
Grandchild

In the Grandchild Table we want to get a value from the Parent Table.
s the article says, we need to create an extra VC in the Child Table and use this VC like a Bridge between the Parent and Child Table.

Could we now use something like [_thisrow-2].[Parent Name] in the Grandchild Table?
I tested a bit but with no luck.
But that would be very helpful and would save the world from too many VCs

Steve

Not to my knowledge. _THISROW-n is not a work-around for dereference chains.

Steve

Back-references within nested queries ([_THISROW-n])

Consider a complex App formula with “nested” SELECT() expressions (SELECT() expressions within SELECT() expressions):

SELECT(
  table[column1],
  ...
  SELECT(
    table[column2],
    ...
    SELECT(
      table[column3],
      ...
    )
    ...
  )
  ...
)

Let’s call the row for which this entire expression is evaluated as its App formula the Origin Row.

Within this entire expression, we can refer to column values of the Origin Row by dereferencing [_THISROW]. For instance, to get the value of the Customer Name column of the Origin Row, we can use [_THISROW].[Customer Name].

Let’s also name each of the nested queries within the entire expression:

Query 1: SELECT(table[column1], ...) (outer-most query)
Query 2: SELECT(table[column2], ...)
Query 3: SELECT(table[column3], ...) (inner-most query)

Query 2 is nested inside Query 1. Within Query 2, column values of the row currently being examined by Query 1 can be accessed by dereferencing [_THISROW-1]. For instance, to get the value of the column1 column of the Query 1 row, we can use [_THISROW-1].[column1]. The -1 in _THISROW-1 refers to the query one step outside the current query.

Query 3 is nested inside Query 2. [_THISROW-1] refers to the query one step outside the current query, so within Query 3, [_THISROW-1] refers to the current row of Query 2. Query 2 is itself nested within Query 1, so Query 1 is two steps outside Query 3. [_THISROW-2] can be dereferenced to access the values of columns in the query two steps otside the current one: Query 1.

Bahbus

I’ve been trying to figure out a good use case, but I was struggling with the concept. But that chart makes it super easy. I hope you can add that image to the support docs at some point.

Steve

I hope to at some point. In the meantime, I’ve added a link to that reply to this FAQ’s index so it’ll have some prominence here.

Bahbus

Come to think of it. Images similar to this would probably help in several of the support docs detailing some of the more complex stuff.

Jake_Naude

Hey @Steve… You’ve expressed a complex thing with succinct elegance here… I’ve been struggling to grasp the ([_thisrow-n]) concept but I get it now… Cheers…

Jerry_Fernandez

Where should this code go? I tried entering this to slice but the result should be yes and no so it didn’t accept.

Rifad

Is there any way to select maximum occurred value from the list of columns from List([col.1],[col.2],[col.3],[col.4],[col.5],…)

Maybe column 1 Have apple, Column 2 have Orange, Column 3 have again Apple so display “Apple” as its occurred more times than orange ?

Steve

Not to my knowledge.

WillowMobileSys

Having similar values across several columns - especially to the point that you would want to count them - is a red flag indicator that you are probably not using the correct table design/structure for your app.

Appsheet is a row-based system, as are most data systems. It is best to use a normalized structure which simply put means you store the data as rows instead of columns so it can easily be processed as the system intended.

To expand on your example. Let’s say your table has columns Fruit1, Fruit2, Fruit3, Fruit4, Fruit5…etc. As soon as you have more fruit than columns, you have an issue. You would need to expand the table with more Fruit columns. But how many more? This is a non-normal form. You can’t know how many columns you need ahead of time. Couple that with the fact that there are no functions to help you count, sum, etc by columns and you will find that there is great difficulty building the app.

Instead, you would want to normalize the data, by creating a structure like so:

Item-Number    Fruit
     1         apple
     2         orange
     3         apple
     4         kumquat
     5         tangerine

This represents the same data, even the column numbers, but as rows.

By normalizing the data, you can add countless rows with no need to change the data structure. And you can then use the system functions to easily select rows for counting, summing ect. Life will be much better!!

I hope this helps!

Rifad

Thanks a lot for your time.

I need to do like column only because I have few criterias and each column have 3 options that is selected from the table like you mentioned above. But for my requirement I need almost 11 column and each column Is different criteria like priority, price, item type etc… but everything has a table that is listed and selected using select function.

Rifad

For you reference. This is the table I’m using.

WillowMobileSys

This is a normalized table. Why can’t you operate on this table as is? Where is it that you are using the “like” columns as suggested in this post above?

Rifad

Here.

WillowMobileSys

This table also looks normalized. I’m confused. Which columns relate to your original question shown above?

Steve

How do I do VLOOKUP()?

In Excel, VLOOKUP() searches the first column in a range for a given search value and returns the value of another column in the same range from the row containing the matching value. The match can be exact or “approximate”. An approximate match is the closest value less than or equal to the search value.

VLOOKUP() with exact match

The AppSheet equivalent to VLOOKUP() using exact match is LOOKUP():

LOOKUP(search-value, "table", "search-column", "result-column")

where search-value is an expression that produces the value to find; table is the name of the table to search; search-column is the name of the column in which to look for the search value; and result-column is the name of the column whose value should be returned if the search value is found in the search column of that same row.

LOOKUP() returns at most only one value, from the first row in which it finds a match.

VLOOKUP() with approximate match

AppSheet has no direct equivalent for VLOOKUP() with approximate match, but this expression provides similar behavior:

LOOKUP(
  ANY(
    ORDERBY(
      FILTER(
        "table",
        ([search-column] <= search-value)
      ),
      [search-column],
        TRUE
    )
  ),
  "table",
  "key-column",
  "result-column"
)

where table is the name of the table to search; search-column is the name of the column in which to look for the search value; search-value is an expression that produces the value to find; key-column is the name of the key column of the table; and result-column is the name of the column whose value should be returned if the value of the search column of that same row is less than or equal to the search term.

The expression above returns at most only one value, the value of the result column from the row with the highest search column value less than or equal to the search value.

Note that this expression is expensive and may hurt app performance.

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT()