/
Datamart Join Sample Code

Datamart Join Sample Code

Owned by Jana Volencová (Deactivated)
Last updated: Mar 05, 2024 by Pavel Matyasko (Deactivated)
1 min read

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)



Related content

Found an issue in documentation? Write to us.