Transformations

Back Next

Rank transformation example

You store customer data in a relational database table. You want send a promotion to the top three customers in each tier. Use a Rank transformation to rank the customers in each tier by order amount.

The source, mapping, and target are configured as follows:

Source Data

The following table shows the source data:

CUST_ID	CUST_TIER	CUST_NAME	ORDER_AMT
10110102	Gold	Brosseau, Derrick	63508.12
10110109	Platinum	Acheson, Jeff	139824.15
10110143	Silver	Cudell, Bob	49614.00
10110211	Silver	Amico, Paul	47677.30
10110215	Platinum	Bergeron, Kim	148871.25
10110224	Silver	Madison, Shelley	40497.10
10110235	Gold	Anderson, Rick	50429.27
10110236	Silver	Tucker, Paul	42585.00
10110237	Silver	Smith, Robert	38563.98
10110393	Gold	Washington, Rochelle	73767.96
10110425	Gold	Nguyen, Trang	65522.25
10110434	Silver	Keane, Thomas	38055.40
10110436	Platinum	Catherwood, Jennifer	117107.44
10110442	Platinum	Charest, Walter	126618.60
10110458	Gold	Coutts, Sylvain	70646.32
10110497	Platinum	Zheng, Wei	191422.00
10110506	Gold	Gonzales, Roberto	79342.90
10110526	Gold	Vanelo, Susan	81978.06
10110528	Platinum	Abedini, John	136506.32
10110530	Silver	Sousa, Maria	10155.42

Mapping Configuration

Configure the mapping as shown in the following image:

The mapping shows Source transformation "src_Customers" connected to Rank transformation "rnk_CustByOrderAmt." The Rank transformation is connected to target "tgt_Top5CustPerTier."

Configure the Rank transformation as follows:

Rank tab: Configure the following properties:

Field
Value

Rank By
ORDER_AMT

Rank Order
Top

Parameterize Number of Rows
Not Parameterized

Number of Rows
3

Field	Value
Rank By	ORDER_AMT
Rank Order	Top
Parameterize Number of Rows	Not Parameterized
Number of Rows	3

Group By tab: Select CUST_TIER as the group by field.

Target Data

The following table shows the data that is written to the target when you run the mapping:

RANKINDEX	CUST_ID	CUST_TIER	CUST_NAME	ORDER_AMT
1	10110526	Gold	Vanelo, Susan	81978.06
2	10110506	Gold	Gonzales, Roberto	79342.90
3	10110393	Gold	Washington, Rochelle	73767.96
1	10110497	Platinum	Zheng, Wei	191422.00
2	10110215	Platinum	Bergeron, Kim	148871.25
3	10110109	Platinum	Acheson, Jeff	139824.15
1	10110143	Silver	Cudell, Bob	49614.00
2	10110211	Silver	Amico, Paul	47677.30
3	10110236	Silver	Tucker, Paul	42585.00

Rename Saved Search

Table of Contents

Transformations

Transformations

Rank transformation example

Rank transformation example

Source Data

Mapping Configuration

Target Data