Return Values from a List

AppSheet AppSheet Q&A
1

Hello all,

In column [Layoff Periods], I have a list of beginning and end dates separated by " | ". Example, 10/20/2010-11/20/2011 | 12/15/2018-10/10/2019 | 12/12/2022-12/13/2022…

In the same row, I have column [Start Date] with a single date. Example, 10/01/2015. If any of the beginning dates in [Layoff Periods] (in this case 12/15/2018, 12/12/2022) occur after [Start Date] of 10/01/2021, I would like to return only those list values so I can then calculate the durations and add those together for a total number of hours.

The list returned in this case would be 12/15/2018-10/10/2019 , 12/12/2022-12/13/2022

The following was my stab at a solution but of course SELECT doesn’t work for a non-referenced list-- correct? Any other ways return values from a list dependent on a condition? Or any other way to go about this?

SUM(
EXTRACTDURATIONS(
SELECT(SPLIT([Layoff Periods], " | "), [Replaced Start Date]<=DATE(CONCATENATE(EXTRACTDATES([Layoff Periods])))
)
)
)

Thanks!

2

Please post such queries in “Q&A” section.

Also may we know how you composed the column [Layoff Periods] with an unusual pattern patter of using two separators “|” and “-” and what is the type of the column. This would enable the community to suggest any possible solution.

1 Like
3

Sorry, first time posting here. Not sure how to move this existing post to that section.

In response to how [Layoff Periods] was formed. It’s of type Long Text. It does not get updated except via an action, a sort of snapshot of the data from another cell before that cell gets erased. Every time that other cell gets a new duration date, it gets added into [Layoff Periods] before being wiped. Each new record in [Layoff Periods] gets added with a “|” to separate it from previous records.

The “-” is meant as a shorthand for a time period between two dates.

4

Thank you. In general working with Long Text as a “pseudo list” for extracting sorted numeric values that too with different value separators such as “-”, “|” will be a very tedious expression, even if it may be achieved.

Instead you may want to consider the following

Please consider using a single separator of " , " with a column type list with base type as “date” while appending the column [Layoff Periods] through actions. So each odd element in the list is “start date” and even element is “end date” of lay off period. That would probably allow the dates to be handled in a better manner.

Then the [Layoff Periods] column could look like this

LIST(“10/20/2010” , “11/20/2011” , “12/15/2018” , “10/10/2019” , “12/12/2022” , “12/13/2022”)

You could then insert the [Replaced Start Date] in the list as

SORT(LIST(“10/20/2010” , “11/20/2011” , “12/15/2018” , “10/10/2019” , “12/12/2022” , “12/13/2022”)+LIST([Replaced Start Date]), TRUE)

The actual expression will be

SORT( [Layoff Periods] +LIST([Replaced Start Date]), TRUE)

The above will sort the dates in descending order that has inserted [Replaced Start Date] as well.

You could then get the desired higher dates with an expression by using FIND() and LEFT() etc as follows

LEFT(
SORT( [Layoff Periods] +LIST([Replaced Start Date]), TRUE) ,

FIND(SORT( [Layoff Periods] +LIST([Replaced Start Date]), TRUE) )-1
)

FIND() - AppSheet Help

LEFT() - AppSheet Help

2 Likes
5

@Suvrutt_Gurjar explains a great approach. Consider two additional possibilities:

  • You likely don’t need the column to be a Long Text type–just Text type ought to suffice. AFAIK, the only distinction is the accommodation of line breaks in Long Text.
  • You could create an additional Layoff Periods table altogether, with separate columns for each period’s begin and end dates.
1 Like