Most recent record prior to ship date

[nburton78]

I have 2 tables, sales information and purchasing status.  The 2 tables connect on the field "lot".  The issue is, that the purchasing status table has multiple records for each lot.  So I'd like to specify that I want to see the most recent inbound status field where the receipt date is before the ship date.  I tried to do this in the straight table with this expression "Max(if(receipt date <=Ship date,inbound status))" but it doesn't retrieve anything.  What am I doing wrong?  Here's a sample of my load script:

table sales:

load 

sales order number

sales line number

sales order type

ship date

lot;

{SQL}

table Purchasing status:

load

lot

receipt date

inbound status;

{SQL}

[sasiparupudi1 · Accepted solution]

Try MaxString

MaxString(if(receipt date <=Ship date,inbound status))

[nburton78]

Thank you that worked perfectly! thank you thank you thank you