filter question

[jmoore]

I have the following output:

USI    Period       Month         Allocated Amount
U1     Historical   FY04Jan     $10
U1     Historical   FY05Feb    $12
U1     Current      FY09Jan     $15

U2     Historical   FY05Feb    $12
U2     Historical   FY09Jan     $18

U3     Current      FY09Jan     $30

I need to filter out the USIs which are 'Current' and have no 'Historical' Period. I.e. I want the records like U3     Current      FY09Jan     $30

I have tried all sorts of boolean expressions but can't seem to get it right. Does anyone have any suggestions? Thank you.

[godaniel]

Are you saying you want something that only has "Current"?  Sounds like you might need to make a view or query subject in FM to provide only data that meets this criteria.

[jmoore]

Yes, only current, if it has historical or historical and current, I don't want it. Thanks.

[dumbell]

If did not get this yet..

Try detail filter like this.It usually works for me

[USI]='U3' and [Period]='Current'

Good Luck

[Gopinath]

Create 2 queries, one having current period USIs and one having Historical USIs.
join(inner) them based on USIs and fetch the Current period USIs from the first query.