Filter records by surveillance data.

mpcastro — Tue, 28 May 2024 14:07:59 GMT

Hey guys.

I have this structure:

Company	Sector	Effective date	Cost center
1	8	01/05/2024	2569
1	8	01/10/2023	2545
1	7	01/04/2024	6352
1	6	01/01/2024	3665
1	6	01/11/2023	3645
1	6	01/10/2023	3609

I have the effective date as a filter.

I need to set up a dashboard with records with a date less than or equal to the filter's effective date, returning only 1 record per key (company,sector).

I've already searched on several forums, but I can't find a solution.
Thanks.

Re: Filter records by surveillance data.

Sayed_Mannan — Tue, 28 May 2024 19:08:12 GMT

Hi, You can achieve this by using the FirstSortedValue function. This function returns the first sorted value of an expression, sorted by another expression.

try this

Load
Company,
Sector,
FirstSortedValue(Effective_date, -Effective_date) as Effective_date,
FirstSortedValue(Cost_center, -Effective_date) as Cost_center
Resident YourTable
Where Effective_date <= YourFilterDate
Group By Company, Sector;

In this script, replace YourTable with the name of your table and YourFilterDate with the date you have as a filter.

Please replace the field names with your actual field names if they are different. This script should give you a new table with filtered records as per your requirement.

I hope this helps!

topic Re: Filter records by surveillance data. in App Development

Filter records by surveillance data.

Re: Filter records by surveillance data.