In SAP BODS what is equivalent process of SQL server LEAD function

•  This Blog explains in sap bods which function is equivalent to SQL Server lead function.
• The requirement is to display next month record against to current record by using SAP BODS.
• In SQL Server we have LEAD function to display next month record against to current record and we don't have LEAD function in Data Services.

SQL server Lead function:

• Lead function is a SQL Server Analytic function.
• Lead function is used where we need to compare one record with an immediate next record.

SAP BODS which function is equivalent to SQL server LEAD function:

I want to compare current month sales_amount with a next month sales amount by doing that I can calculate how much more sales come into picture.

Source table:

• We have Source table like this.

This blog Explains step-by-step process

• First we need to create Project, JOB as JOB_NXT_MNTH_SALES and then we need to create Work Flow as WF_NXT_MNTH_SALES and then create Data Flow as DF_NXT_MNTH_SALES.

• Drag Source table into Data Flow and then place Query Transformation with Rename as QRY_ORDERBY and here we select the columns MONTH_NUMBER, YEAR_NUMBER & SALES_AMOUNT from the Schema In of the Query transform and Map to Output to the Schema Out.
• Then we need to sort the data by using MONTH_NUMBER, YEAR_NUMBER as Ascending Order and SALES_AMOUNT as Descending Order because of if we sort the SALES_AMOUNT data as Ascending order then it displays Previous month sales amount in the current row, actually we want to display Next month Sales amount in the current row so that we sort the SALES_AMOUNT data as Descending Order.

• Next we need to place another Query Transformation as QRY_IS_GRP_CHNGD and then we need to insert new output column as “IS_GRP_CHNGD” with int data type in the Schema_Out.

• Next we define the Mapping value as is_group_changed( QRY_ORDERBY.YEAR_NUMBER)

is_group_changed():

1. is_group_changed(COLUMN) will return ‘1’ if the previous value does not match the current one.
2. This function is used to Partition the Data into groups based on the Column.

• Next define the Mapping to all the fields and then insert the new output column as "PREV_SALES" with int data type in the Schema Out.

• Next we define the Mapping value as previous_row_value( QRY_IS_GRP_CHNGD.SALES_AMOUNT)

previous_row_value():

1. By using previous_row_value() function we can extract the data set from a previous row for the current row.

• Next we can place one more Query Transformation as Query_NEXT_MNTH_SALES and then Map to out MONTH_NUMBER_YEAR_NUMBER & SALES_AMOUNT from Schema In to Schema Out and insert one more new output column as "NEXT_MNTH_SALES_AMNT" with int data type in the Schema Out.

• Next we define the Mapping value as decode(QRY_PREV.IS_GRP_CHNGD=1,0,QRY_PREV.PREV_SALES)

decode():

Decode function works like if-then-else.

• Next need to place another Query transform and should give direct mapping to all the fields and need to sort the data based on MONTH_NUMBER & YEAR_NUMBER because of data looks compatible manner after sort the data.

We can see in the below image our required output.