Return one date for Calender

AppSheet AppSheet Q&A
,
1

My table contains possible multiple date entries per ID. Using a row filter on a slice, I would like to return only one date if there is more than 1 date entry per ID for the calendar. Can be TOP or ANY. Here’s what I have so far:

End Date = ANY(SORT(SORT(Job Logs[Job ID], FALSE)(Job Logs[End Date]), TRUE))

I don’t believe I am using this in the correct manner as it should return all of the different dates per ID, but only one of each of the same dates.

2

Hi @AI-FL

What about that ?

ANY(
  SELECT(Job Logs[End Date],
    [_THISROW].[Job]=[Job ID]
  )
)

For reference:

1 Like
3

It is returning a reference of Log ID in the test. Log ID is the key column but not the column I am selecting by. Should be Job ID.

[End Date]=ANY(SELECT(Job Logs[End Date],[_THISROW].[Job ID]=[Job ID]
)
)

(The value of column ‘End Date’) is equal to (One randomly chosen value from this list (
…The list of values of column ‘End Date’
…from rows of table ‘Job Logs’
…where this condition is true: ((The value of ‘Job ID’ from the row referenced by ‘Log ID’) is equal to (The value of column ‘Job ID’))))

4

AI-FL:

[End Date]=ANY(SELECT(Job Logs[End Date],[_THISROW].[Job ID]=[Job ID]> )> )

This is of type Yes/No.
You are basically comparing two dates here.
You said you are looking to get a date ?

Can you share as screenshot of your table structure Job Log ?

1 Like
5

3X_8_2_82bc4b565595d43689c9c173e8c3483828eeb8a0.png
3X_8_2_82bc4b565595d43689c9c173e8c3483828eeb8a0.png1516×223 35.3 KB

For rows 3 and 4, the date is the same AND the Job ID is the same. I want to return only one of these dates.

For this table the calendar would only show 5 records total. The Job ID 9bd643b9 would have two only because they are different dates.

1 Like
6

OK,

Your slice settings is unclear to me, so I’m not sure of what you are trying to achieve.
Plus, this:

AI-FL:

It is returning a reference of Log ID in the test. Log ID is the key column but not the column I am selecting by. Should be Job ID.

That’s normal, this is a row filter condition and your Log ID is the key_column of the table to which you are applying the filter.

Are you trying to get only one item per row having the same job ID and same End date ?
If so, I suggest you add a virtual column:
“isDuplicateEndDate”, type Yes/No, with expression:

[_THISROW]<>
ANY(
  TOP(
    FILTER("Job Logs",
    AND(
      [_THISROW].[End Date]=[End Date],
      [_THISROW].[Job]=[Job ID]
    ),1
  )
)

Then, you row filter condition would be:
NOT([isDuplicateEndDate])
Please note this is an expensive expression, as the FILTER expression runs on the whole table for each row. You may want to use a workaround in order to set maybe a real column value that will set “yes” if the current log have yet a same jobId/EndDate duet in the table.

2 Likes