Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Dates
  6. Functions
  7. Creating Custom Functions
  8. Custom Function API Reference
  9. POWERCENTERHELP

Transformation Language Reference

Transformation Language Reference

LEAD

LEAD

Returns the value that is an offset number of rows after the current row in an Expression transformation. Use this function to compare values in the current row with values in a future row when you run a mapping on the Spark engine in the Hadoop environment.
A lead value appears after the current row in a set of data.
When you use LEAD in a transformation, you must configure the transformation for windowing. Windowing properties define how the data is partitioned and ordered.

Syntax

LEAD (
column_name
,
offset
,
default
)
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
column_name
Required
The target column or expression that the function operates on.
offset
Required
Integer data type. The number of rows after the current row to obtain a value from.
default
Optional
The default value to be returned in case the offset is outside the bounds of the partition or table. Default is NULL.

Return Value

The data type of the specified
column_name
.
Default
if the return value is outside the bounds of the specified partition.
NULL if
default
is omitted or set to NULL.

Examples

The following expression returns, for each employee, the date the next employee was hired:
LEAD ( HIRE_DATE, 1, NULL )
EMPLOYEE
HIRE_DATE
RETURN VALUE
Hynes
2012/12/07
2014/05/18
Williams
2014/05/18
2015/07/24
Pritchard
2015/07/24
2015/12/24
Snyder
2015/12/24
2016/11/15
Troy
2016/11/15
2017/08/10
Randolph
2017/08/10
NULL
There is no lead value available for the last row, so the function returned the default value of NULL.
The following expression returns the difference in sales quota values between the first quarter to the third quarter of two calendar years:
LEAD ( Sales_Quota, 2, 0 ) - Sales_Quota
You partition the data by year and order by quarter.
YEAR
QUARTER
SALES_QUOTA
QUOTA_DIFF
2016
1
300
7700
2016
2
7000
0
2016
3
8000
0
2017
1
5000
4000
2017
2
400
0
2017
3
9000
0
The lead values of the second and third quarter are outside the specified partition, so the function returned a value of "0."