Applying Master Rates through a Join rather than Append Field

We came across an analytic test scenario recently where 'Master Incentive Rates' were being applied to a 'Customer Billing' dump. The 'Master Incentive Rates' in this case were influenced by a number of determining variables (fields) like Product Code, Customer Number, Quantity Sold, Business Unit and City (Branch).

 

With 5 variables (fields) influencing the correct application of 'Master Incentive Rates' we noticed that writing an @compif() criteria through Append Field would mean building a complex condition through the Equation Editor like below ~

 

@compif(PROD CD=="A01" .AND. CUST REF == "C005" .AND. BU = 245 .AND. BRANCH == "MUM" .AND. @between(BILL QTY, 0, 500), 10, ....)

 

In the criteria above only one possible combination of the masters is applied. There were close to 250 such combinations.

 

Now imagine placing these 250 combinations in an @compif() criteria and then validating the same for potential Equation Editor errors. Further if the underlying masters change in the future, it would mean an edit on the saved criteria at the right place in this super large criteria.

 

For this reason we felt an easier way to manage this analytic requirement is to build the masters in a simple MS-Excel file with the columns - PROD CD, CUST REF, BU, BRANCH, FROM BILL QTY, TO BILL QTY and SALE INC RATE PCT

 

This is a one time effort to build the master plus change management on the MS-Excel file is easier.

 

Now once we import the master file into IDEA it is a simple JOIN operation with the 'Customer Billing' dump on the determining fields like PROD CD, CUST REF, BRANCH, BU etc. This would help us plot the correct Sale Incentive Rate against the right sale transaction.

 

So decide for yourself based on the complexity of the Masters if you need to perform a simple Field Manipulation - Append Field or Join.

 

Best Regards

 

Group Admin Team