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)
Configuration Engineer Knowledge Base

Datamart Join Sample Code

Analytics

Related content
