Hi.
I’m pretty new to queries so looking for a little help, please.
I’m creating a dashboard to show a variety of part information, one of which is VendPart_VenPartNum. My problem is that there are multiple supplier price lists on the system for some suppliers, so I’m getting multiple lines through for some parts. Can anyone advise me how to filter my query to only show the most recent price list?
Our price lists have an Effective Date, but no Expiration Date, so I can’t use that as a filter.
Thanks in advance.
Which tables u r using in BAQ ?
Sorry, should have said:
Part
PriceLstParts
PartWhse
PartCost
VendPart
[quote=“EChristy, post:1, topic:38020”]
Our price lists have an Effective Date
[/quote] So this is the only date which updates on new price change, and available to play in BAQ …right ?
Yes, that is correct.
Okey…then try :
In BAQ designer, select ur BAQ, go to Display -> sort order -> select your Table (e.g VendPart)-> select EffectivDate -> arrange it by Descending order (Down Arrow - IMP)
now come back to Phrase builder, select your table of ‘EffectiveDate’ (e.g VendPart ),right click on VendPart, -> select Query item -> First.
Ultimately, query will desplay first item from sorted (Descending- Latest date first basis) dates .
try ur luck 
Thanks for that. Apologies for the late response.
I don’t have the Query Item option when I right click the table - only the Remove Table option.
Thanks for your suggestion though.
can u post ur Phrase builder screenshot ? Only Remove table option is appears only when u r using SQL view in BAQ.
Thanks
Oh, sorry…U r on E10…ys, there is no provision for selecting FIRST / LAST item by Right Clicking on Table icon. I am not sure but, there is some ways for doing that in subQyery criteria. I am also finding for solution…
Thanks
The simplest way I have found of getting the first row is to use the ROWNUM calculation in a sub-query:
ROW_NUMBER() OVER(PARTITION BY InvoiceNum, InvoiceLine ORDER BY InvoiceNum)
In this instance each time the invoice number and invoice line number changes the row number will reset to 1
Then when you pull that through to the Top Level query you filter on Row Number = 1 and that gives you the first record.
2 Likes