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.

Found an issue in documentation? Write to us.