Select keyid for specific column not duplicate?

AppSheet AppSheet Q&A
,
1

If i have a table as show below.

ID Name Type Qty
1 A Pork 1
2 B Pork 2
3 C Milk 3
4 D Milk 4

How should i select only not duplicate in column Type ?

If i use SELECT([Relate test][Type],True,True) , i only got list of Pork,Milk. But what i want is list of ID 1,3.

2

Please post a screenshot of your expression.

3

3X_f_e_fe3e5a7958bf28cda00add3285998e94b61a38a6.png
3X_f_e_fe3e5a7958bf28cda00add3285998e94b61a38a6.png1119×981 49.7 KB

3X_8_d_8d93cbe77337158c2709f5aefecefbbe0fed1bc7.png
3X_8_d_8d93cbe77337158c2709f5aefecefbbe0fed1bc7.png1745×458 19 KB

3X_d_3_d36336026b82efbd9b1fac2af424ee60df2f7f66.png
3X_d_3_d36336026b82efbd9b1fac2af424ee60df2f7f66.png911×812 24.2 KB

I just want to select any key row of each Type.

1 Like
4

Maybe this?

SELECT(
  [Related tests][ID],
  ISBLANK(
    FILTER(
      "tests",
      AND(
        ([_ROWNUMBER] < [_THISROW-1].[_ROWNUMBER]),
        IN([ID], [_THISROW].[Related tests]),
        ([Type] = [_THISROW-1].[Type])
      )
    )
  )
)
1 Like
5

Work like miracle !!! Thank you very much, @Steve

I need to learn more about Filter now. Could you please explain more in details about this formula ? Why there have ISBLANK ? I am a bit confused.

1 Like
6

The approach I’ve used is to include in the result only the first row of the tests table for each [Type] found in the rows identified by [Related tests].

The SELECT() expression looks through each row in [Related tests]. For each row, the FILTER() expression is performed to list the rows in tests that have the same [Type] value and that occur in the [Related tests] list and that have a lower row number. If there are no such rows (the result of FILTER() is an empty list, or “is blank”), we know that the row we’re examining is the one with the lowest row number for that [Type] value.

1 Like
7

Thank you very much, @Steve

1 Like