I am trying to pull the Last sale date of a part. The Last Sale Date could be a MFG-CUS or a STK-CUS. I understand how to create the calculated field and the subquery but what I cannot figure out is how to say:
Pull the LAST Tran Date be it for MFG-CUS or STK-CUS but not both.
Seems so easy, but I feel I have tried every way I can think of but am not getting the results I’m expecting.
Something like Select PartNum, max(transactiondate) from erp.parttran where trantype in(‘MFG-CUS’,‘STK-CUS’) GROUP BY PartNum
That’s exactly what I was going to suggest.
I thought I was doing that already…I’ll look again!
If I find that I am doing it (but maybe not correctly), would it be okay to post the BAQ for your review?
OMG - I figured it out! It was because I was having the subquery list the Trantype and Trandate instead of letting the calculated fields return those transactions.
When I took out PartTran.TranType and PartTran.TranDate, I received the expected results.
Thank you for your responses - I really appreciate your help!
Well…I thought I had it but found that it was returning incorrect dates. It is pulling one date for some parts, but for other parts it seems to be pulling multiple random dates.
Could I upload the BAQ for your review?
As Alisa said try something like this.
Create your sub query and set the select criteria on the TranType
Add PartNum to your display and Group BY
Create a calculated field for MaxTranDate
Thank you for your wonderful example - I’ve set up the Subquery as recommended. On the Top Level I have the table “Part” as I am asking for only manufactured parts and that I don’t want certain part numbers included in the result.
I am connecting the SubQuery to Parts using PartNum.
I am still receiving multiple “LastTranDates” for a single part - oh, and I am grouping by PartNum.
Sorry,
My bad should have said,
Step 1
Create top level Query
Add Part Table
Add Field PartNum
Filter on Type “M”
Add second SubQuery for Transactions
Add PartTran Table
Filter on TranType “MFG-CUS” & “STK-CUS”
Add Fields Company, PartNum
Create Calculated Field for LastTran
Group By Company, PartNum
Add sub Query2 to top level query
Link on Company, PartNum
Join type Matching rows from Part and SubQuery2 to see only parts that have a transaction
Run the Query
Wow - looking good so far. I learned some finite details that I wasn’t making. Seems that I had the correct choices but did not have them pulling in the correct order.
I’m going to review the results to verify I have what I need. I’ll mark this as solved as soon as I check my results - but looks great so far!
Thank you again - All looks GREAT!
