Select one row from each group for a slice

[Jake_Naude]

Hi,

I’m writing an expression for a slice and am trying to filter the survey table data to:

• just one row (any)
• from each group of [survey_number] rows
• where the [branch_A_asset_list] row is not blank.

This is as far as I’ve got… but it’s not doing the job…

[_RowNumber] =
MAX(
SELECT(
        survey[_RowNumber],
AND(
		[survey_number] = [_THISROW].[survey_number],
        [lcs] = [_THISROW].[lcs],
		[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
		[select_asset_type] = [_THISROW].[select_asset_type],
        ISNOTBLANK([branch_A_asset_list])
		)))

.
.
From this example I’m trying to end up with just three rows - one from each highlighted area?

survey table

Thanks in advance…

[Austin]

Not sure if this is possible as I have yet to find a way to do anything remotely close to a group by in appsheet. You would need at least a handful of nested selected and logic statements to achieve this functionality. Maybe there would be a way to count based on [branch_A_asset_list] and then return all that are equal to 1? I don’t have a solution to counting based on the column but could be a route to try.

[Jake_Naude]

Maybe there would be a way to count based on [branch_A_asset_list] and then return all that are equal to 1?

Hi @Austin_Lambeth,

Thanks… I’ll try get my head round that and will report back if I have any joy… Cheers…

[Austin]

There might be a post on doing that.

[Jake_Naude]

Austin_Lambeth:

Maybe there would be a way to count based on [branch_A_asset_list] and then return all that are equal to 1?

Hey @Austin_Lambeth,

You got me thinking and I went back to the data and found an existing helper-column called [do_not_fire] that works for this case. [do_not_fire] leaves the first of each batch of rows blank and each other row in the batch gets a number.

Although it’s a workaround, this expression now gives the desired result… Thanks for the nudge…

IN(
		[id],
SELECT(
        survey[id],
AND(
		[survey_number] = [_THISROW].[survey_number],
        [lcs] = [_THISROW].[lcs],
		[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
		[select_asset_type] = [_THISROW].[select_asset_type],
        ISNOTBLANK([branch_A_asset_list]),
        ISBLANK([do_not_fire])
		)))

[Jake_Naude · Accepted solution]

Hey @Austin_Lambeth,

I found it… This one returns one row from each group of rows…

[_RowNumber] = 
MAX(
SELECT(
  	survey[_RowNumber],
	AND(
	[lcs] = [_THISROW].[lcs],
  	[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
  	[select_asset_type] = [_THISROW].[select_asset_type],
  	[survey_number] = [_THISROW].[survey_number],
  	ISNOTBLANK([branch_A_asset_list])
    )))