Commented Groovy Example
The following example explains some of the Groovy features you can use in PFX calculation logics.Â
We have competitor information stored in a Product Extension called 'Competition', along with a price and validity date. There can be multiple records per competitor, each with different validity and price. We want to create a table with each competitor and its latest price (highest validity date). If two records have the same validity date, pick the higher one.
The input data is in a Product Extension called 'Competition' and the structure looks like this:
SKU | Competitor (attribute1) | Date (attribute2) | Price (attribute3) |
---|---|---|---|
00001 | Volvo | 2016-01-01 | 5698.5 |
00001 | Volvo | 2016-06-01 | 5702.0 |
00001 | Iveco | 2016-03-01 | 6041.0 |
00001 | Iveco | 2015-12-01 | 6100.2 |
00001 | MAN | 2016-05-01 | 5998.0 |
00001 | MAN | 2016-05-01 | 5713.5 |
The final result should look like this:
Competitor | Price |
---|---|
Iveco | 6041.0 |
MAN | 5998.0 |
Volvo | 5702.0 |
This is how it could be implemented in Groovy:Â
// get the look-back perios - number of months to look back // utilize the Elvis operator - in case the user entry is null or zero, lookback will be 12 def lookback = api.decimalUserEntry('Look-back Period [months]') ?: 12 // create the look-back date by utilizing Joda DateTime and subtracting the look-back period from the current date def lookbackDate = new DateTime().plusMonths(-lookback.toInteger()).toDate() // get all competitor records from the Product Extension whose validity is greater or equal to the look-back date // we need to convert the date to its String representation because in the PX all attributes are stored as Strings def competitors = api.productExtension('Competition', Filter.greaterOrEqual('attribute2', lookbackDate.format('yyyy-MM-dd'))) // the competitors will evaluate to true if it's a non-null and non-empty collection if (competitors) { // sort the competitors by the date (ascending) first and price (ascending) second using a custom comparator // use a Groovy Closure for that - a, b represent the two objects being compared def sortedCompetitors = competitors.sort{ a, b -> // the dates are stored as Strings in format 'yyyy-MM-dd' so it's safe to compare them lexicographically def date1 = a.attribute2 def date2 = b.attribute2 // we need to convert the prices from String to BigDecimal for it to be compared properly // the save navigation operator ?. will evaluate the entire expression to null if attribute3 is null, preventing a Null Pointer Exception def price1 = a.attribute3?.toBigDecimal() def price2 = b.attribute3?.toBigDecimal() // the Spaceship operator delegates to the compareTo method of its operands // notice how we combine it with the Elvis operator // if the comparison of the dates is zero (equal dates), which evaluates to false, it will compare the prices return date1 <=> date2 ?: price1?.toBigDecimal() <=> price2?.toBigDecimal() } // here we convert the list of competitors sorted by date and price to a map where the competitor name is the key and the competitor record is the value // the result will be a map where each competitor will have the latest price // that's because we sorted the list earlier in ascending order, so each record with later date or higher price overwrites the previous one def latestPricePerCompetitor = sortedCompetitors.collectEntries { // the left side will evaluate to the String representation thanks to the parentheses // if you don't explicitly name the iterator in the Closure, you can reference it by 'it' [ (it.attribute1): it ] } // we now create a matrix which will have three columns def columns = [ 'Competitor', 'Date', 'Price' ] // since the method api.newMatrix doesn't accept a List but an array of Strings, we use the Spread Operator to unwrap each element of the List and pass it to the method // (of course we could type in the column names directly but we needed to demonstrate the Spread operator) def matrix = api.newMatrix(*columns) // again, we use a Closure to iterate through each element of the map // but this time we rename the default 'it' to 'c' to demonstrate this feature latestPricePerCompetitor.each{ name, record -> // we add a row to the matrix for each entry in the map matrix.addRow([ 'Competitor' : name, 'Date' : record.attribute2, 'Price' : record.attribute3 ]) } // return the matrix return matrix } return null
Found an issue in documentation? Write to us.
Â