How to Format Datamart Query Results

Question

Joining two queries below to aggregate values at an item level and at an invoice level.

What piece of code is needed to structure the output to be formatted as the example desired result:

x = Quantity
y = Price
z = Order Value

Desired result:

[
[x:10, y:12, z:120, customer: 'Green', customer type: 'Good'], 
[x:11, y:13, z:130, customer: 'Red', customer type: 'Medium']
]

Queries:

// Get datamart context
def dmContext = api.getDatamartContext()

// Create datamart query1
def dmQueryItem = dmContext.newQuery(dmContext.getDatamart("Transactions"), false)
dmQueryItem.
select("INVOICE_NUMBER").
select("ACCOUNT_NAME").
select("LOWIEA").
select("QUANTITY_BUOM").
select("FINAL_GIVEN_NET_PRICE").
select("FINAL_GIVEN_NET_PRICE_UNIT").
where(Filter.equal("INVOICE_DATEYear","2018"),
Filter.equal("SOLD_TO", "12345678"),
Filter.equal("SALES_ORGANIZATION","12345"),
Filter.equal("MATERIAL", "XYZ"))

// Create datamart query2
def dmQueryInvoice = dmContext.newQuery(dmContext.getDatamart("Transactions"), false)
dmQueryInvoice.
select("INVOICE_NUMBER").
select("ACCOUNT_NAME").
select("LOWIEA").
select("QUANTITY_BUOM").
select("FINAL_GIVEN_NET_PRICE").
where(Filter.equal("INVOICE_DATEYear","2018"),
Filter.equal("SOLD_TO", "12345678"),
Filter.equal("SALES_ORGANIZATION","12345"))

def joinFields = [:]
joinFields.put ("INVOICE_NUMBER", "INVOICE_NUMBER")        //Join on Invoice#
joinFields.put ("ACCOUNT_NAME", "ACCOUNT_NAME")            //Join on Account Name
def dmQuery1 = dmContext.newQuery(dmQueryItem, dmQueryInvoice, joinFields, false)
dmQuery1.
select("QUANTITY_BUOM_1", "Quantity").                           //Quantity from dmQueryItem
select("FINAL_GIVEN_NET_PRICE_UNIT_1", "Price").       //Price from dmQueryItem
select("FINAL_GIVEN_NET_PRICE_2" , "Order Value").     //Order Value from dmQueryInvoice
select("ACCOUNT_NAME_1" , "Customer").                        //Customer from dmQueryItem
select("LOWIEA", "Customer Type")                                     //Customer Type from dmQueryItem


// Run query and save result to memory
def result = dmContext.executeQuery(dmQuery1)
if (result) {
def tableContext = api.getTableContext()
tableContext.createTable("Transaction_Current", result)
}


if (api.isInputGenerationExecution()) return

def tableContext = api.getTableContext()
def query = tableContext.newQuery("Transaction_Current")
query.
select("Quantity").
select("Price").
select("Order Value").
select("Customer").
select("Customer Type")

def result1 = tableContext.executeQuery(query)
result1?.getColumnValues(0)

isInputGenerationExecution is supported from version 10.0. In older versions use isSyntaxCheck.

Answer

Firstly, regarding the queries and accessing the PA Data in the logics I would suggest you look into this: Query a Datamart. There are examples how you can do all those things. If you would feel more comfortable with SQL we even have that option (that was not available at the time the article was created so it is not mentioned there), you can explore this API function which gives you the potential of the standard SQL.

Secondly, regarding your specific problem, one of the options would be using one of the methods mentioned in that article in chapter "Processing Data", using either result?.getData()?.forEach { row -> where you can fill your lists and maps in any format you need, or just use result?.getData()?.collect() and then just iterate through this map and transform it to anything you need.