Datamart Join Sample Code
This is one way of doing joins, the alternative is to use DatamartContext.executeSqlQuery() and use standard ANSI SQL instead. See also Groovy Consoles.
def sku = api.product("sku")
// Get datamart context
def ctx = api.getDatamartContext()
def dm = ctx.getDatamart("TX_Data")
// Create datamart query
def qInvoices = ctx.newQuery(dm, false)
.select("Sku")
.select("SalesOrder")
.select("UnitSellPriceExFreight")
.select("UnitSellingPrice")
.select("CostExSupplier")
.select("UnitCostExSupplier")
.select("Quantity")
.select("UnitCostAvg")
.select("UnitPriceList")
.select("Segmentation_FINAL")
.select("Quoted_Business")
.select("ListPrice")
.select("Sales")
.select("MarketingGroup")
.select("ProductCode")
.select("TransactionDate")
.where(Filter.isNull("SegmentationExclusions"),
Filter.equal("Sku", sku),
Filter.equal("Active", "Yes"),
Filter.equal("Team1", "No"),
Filter.notEqual("SalesWarehouse", "95EX"),
Filter.notEqual("ProductSpecial", "Yes"),
Filter.isNull("Quoted_Business"),
Filter.notIn("InvoiceType", "Credit", "Credit Note"),
Filter.notEqual("Quantity", 0),
Filter.notEqual("SalesCost", 0)
)
// Create datamart query
def qCreditNotes = ctx.newQuery(dm, false)
.select("Sku")
.select("SalesOrder")
.select("UnitSellPriceExFreight")
.select("UnitSellingPrice")
.select("CostExSupplier")
.select("UnitCostExSupplier")
.select("Quantity")
.select("UnitCostAvg")
.select("UnitPriceList")
.select("Segmentation_FINAL")
.select("Quoted_Business")
.select("ListPrice")
.select("Sales")
.select("MarketingGroup")
.select("ProductCode")
.select("TransactionDate")
.where(Filter.isNull("SegmentationExclusions"),
Filter.equal("Sku", sku),
Filter.equal("Active", "Yes"),
Filter.equal("Team1", "No"),
Filter.notEqual("SalesWarehouse", "95EX"),
Filter.notEqual("ProductSpecial", "Yes"),
Filter.isNull("Quoted_Business"),
Filter.notIn("InvoiceType", "Credit", "Credit Note"),
Filter.notEqual("Quantity", 0),
Filter.notEqual("SalesCost", 0)
)
def joinFields = [
Sku : "Sku",
SalesOrder: SalesOrder
]
def qCreditNoteAware = ctx.newQuery(qInvoices, qCreditNotes, joinFields, false)
.select("Sku_1", "Sku")
.select("SalesOrder_1", "SalesOrder")
.select("((UnitSellPriceExFreight_1 * Quantity_1) + IfNull(UnitSellPriceExFreight_2, 0)) / Quantity_1", "UnitSellPriceExFreight")
.select("((UnitSellingPrice_1 * Quantity_1) + IfNull(UnitSellingPrice_2, 0)) / Quantity_1", "UnitSellingPrice")
.select("CostExSupplier_1", "CostExSupplier")
.select("UnitCostExSupplier_1", "UnitCostExSupplier")
.select("Quantity_1", "Quantity")
.select("UnitCostAvg_1", "UnitCostAvg")
.select("UnitPriceList_1", "UnitPriceList")
.select("Segmentation_FINAL_1", "Segmentation_FINAL")
.select("Quoted_Business_1", "Quoted_Business")
.select("ListPrice_1", "ListPrice")
.select("Sales_1 + IfNull(Sales_2, 0)", "Sales")
.select("MarketingGroup_1", "MarketingGroup")
.select("ProductCode_1", "ProductCode")
.select("TransactionDate_1", "TransactionDate")
// Run query and save result to memory
def result = ctx.executeQuery(qCreditNoteAware)
if (!result) {
api.throwException("Cannot perform query qCreditNoteAware")
}
def tableContext = api.getTableContext()
tableContext.createTable("Transaction_NonQuoted", result)
def timeStart = out.TimeFrameStart
def listPrice = out.ListPrice
def query = tableContext.newQuery("Transaction_NonQuoted")
.select("UnitSellPriceExFreight")
.where("TransactionDate >= '$timeStart'",
"Quantity > 0",
"UnitSellPriceExFreight > 0",
"UnitSellingPrice < $listPrice"
)
result = tableContext.executeQuery(query)
result?.getColumnValues(0)
Found an issue in documentation? Write to us.
Â