Overview
The KEEP() function in SQL is used in conjunction with aggregate functions to return specific values from a group of rows based on a defined ordering. In simpler terms, it allows you to extract a particular value (such as the first or last entry) within a grouped dataset, according to a specified sort order. This is particularly useful when you need to retrieve the most recent or earliest record in a set, like identifying the latest transaction for each customer.
Understanding the Syntax
The general syntax for using the KEEP() function with an aggregate function is:
AGGREGATE_FUNCTION(column_name) KEEP (DENSE_RANK FIRST | LAST ORDER BY column_name)
- AGGREGATE_FUNCTION: An aggregate function such as MAX(), MIN(), SUM(), etc.
- column_name: The column from which you want to retrieve the value.
- DENSE_RANK FIRST | LAST: Specifies whether to return the first or last value in the ordered group.
- ORDER BY column_name: Defines the column by which the rows are ordered to determine the first or last value.
For example:
Suppose you want to create a Saved Search to find the most recent Expense Report submitted by each Employee.
Steps:
- Create a Transaction Saved Search:
- Navigate to Lists > Search > Saved Searches > New.
- Select Transaction as the search type.
- Set Criteria:
- Main Line = True
- Type = Expense Report
- Define Results:
- In the Results tab, add the following fields:
- Name (set Summary Type to Group).
- Formula Date (set Summary Type to Maximum).
- Formula: MAX({trandate}) KEEP (DENSE_RANK LAST ORDER BY {datecreated})
Explanation:
- MAX({trandate}) retrieves the maximum transaction date.
- KEEP (DENSE_RANK LAST ORDER BY {datecreated}) ensures that, in cases where multiple expense reports share the same transaction date, the most recently created one (based on datecreated) is selected.
By incorporating the KEEP() function in your saved search, you can efficiently identify the latest expense report for each employee, enhancing your ability to monitor and manage expense submissions effectively.
In summary, the KEEP() function is a powerful tool in SQL that, when combined with aggregate functions, allows you to extract specific values from grouped data based on a defined order. By mastering the KEEP() function, you can enhance your data analysis capabilities, enabling more precise and insightful reporting within NetSuite.
-----
If you find this information useful, let us know by reacting or commenting on this post!