Sorry for the Bump - looks like this got orphaned on the old board
I’m trying to make a BAQ (eventual for a dashboard) of customers with columns for most recent activity (last: order date, quote date, and invoice date).
My BAQ consists of an outer join between the Customer and OrderHed tables. Using an outer join because I want records from Customer even if no OrderHed records are found.
I made the OrderHed table a summarized table, I so I won’t get a row for every order. But fields from a summarized table have to be in a calculated field, and the there’s no MAX() function. Only TOTAL(), AVG() and COUNT().
Next I tried making the OrderHed non-summarized and changing the selection to LAST. Query phrase:
FOR EACH Customer where ( Customer.EstDate >= 01/01/2015 OR not(Customer.ShortChar01 = '')) no-lock , last OrderHed outer-join
WHERE (Customer.Company = OrderHed.Company and Customer.CustNum = OrderHed.CustNum) no-lock
BY Customer.CustNum by OrderHed.OrderDate Desc.
But this doesn’t retrieve the last OrderHed, Just for kicks and giggles, I changed it to FIRST, and that doesn’t grab the first OrderHed either.
My end goal is to have it also return the last QuoteHed.DateQuoted, and InvcHead.InvoiceDate too. But I cant even get it to work with just the OrderHed.
Since this will end up in a Dashbord, is there a better way to make the dashboard do this summarization? Maybe 4 BAQ’s (one for Customers, and 3 for last OrderDate by Custnum, DateQuoted by CustNum, etc …) .
If MAX / MAXIMUM doesn’t help I think you are stuck for now with BAQ’s… you might be able to hack something together using a BPM.
I’ve done something like this before where we “summarize” some data using a BPM or an external process and put it in a UD Table.
Have you looked at executive queries? I can’t remember if these are available in 8.03 but they have better “summarization” capabilities
Yes, my mistake. In Vantage 8.03 BAQ’s, in the Calculated Fields you are limited to TOTAL, AVG and COUNT as you first stated. You cannot achieve what you want to do in a BAQ query alone in version 8.03. My mind was in BPM land with somewhat full access to 4GL functions.
In thinking more about it, you may have to just suck it up and make the BAQ with non-summarized data and pump that into a Crystal Report and use CR’s Max() function. If they don’t want to print the report, they can always preview it and it may suffice as a quasi-dashboard for you.
We are on V8.03 and have this query working for the latest invoice for a customer:
for each Customer no-lock , last InvcHead where (Customer.Company = InvcHead.Company and Customer.CustNum = InvcHead.CustNum) no-lock .
Can you explain why your query doesn’t work (what record is it retrieving as opposed to the one you actually want)? I know it wouldn’t in our system as I would want the furthest OrderRel.DueDate and that wouldn’t necessarily be the latest OrderHed raised.
Here’s any example:
(I added the condition of CustID=‘WIL96’ just to limit the results for this example. The same results happen w/o the CustID limit)
BAQ with Phrase: for each Customer where ( Customer.CustID = 'WIL96') no-lock , each OrderHed where (Customer.Company = OrderHed.Company and Customer.CustNum = OrderHed.CustNum) no-lock .
returns:
BAQ with Phrase: for each Customer where ( Customer.CustID = 'WIL96') no-lock , last OrderHed where (Customer.Company = OrderHed.Company and Customer.CustNum = OrderHed.CustNum) no-lock .
Returns:
OrderNum 2120133 is obviously not the last by this customer
And the reason for going by orderNum or OrderHed.OrderDate, was to show when the last time an order was entered for the customer.
All of this was to try and find only the customers with recent activity, so that we could port them to E10.
But now I’m mostly curious as to why Order 2120133 is the “last” record (for that customer) and not the 2120133. Is it because 2120133 might have been the last one edited?
FWIW I have had the same unexpected / inaccurate results with using FIRST
and LAST in version 9.05. I have found I cannot reliably use those
aggregates as they rarely return what I need.
I know if I were in your shoes I would want to just try to figure this out, but is there a reason why you could not just do a data dump and use excel to fix your MAX and be done with it?
A solution was found days ago (and marked as such). I’ll do it in either Excel or CR.
I’m just to damn curious to let things go. Especially if I thought it was me that was doing something wrong. The thread evolved into “Why doesn’t LAST give you the last record expected?”.
If you have mod powers, please “close” this thread and put it to rest
