Can you use an IF statement in a filter?

[Lisa_M]

Hi Chaps

I have a dataset I'm receiving each month where I have two similar fields - Reporting Period and Month.  In this data set, I am effectively getting one month of NEW transactions each month and eleven months of prior period transactions.  However, the prior period transactions may be different to what's been received in the prior periods as users of the source system have the ability to overwrite existing records.

What I'm trying to do with the dataset is to split the transactions into one of two groups - effectively, if they're NEW transactions, they follow a single process.  If they're prior period transactions, they are compared to what we've received previously to determine if an adjustment is required.

How I'd like to do this is using a filter, which effectively evaluates this expression as true or false for each transaction:

IF [REPORTING PERIOD]=[MONTH} then 'TRUE' else 'FALSE' ENDIF

I know I could use a formula tool and add this in as an extra field, but honestly, I was wondering if it's possible to use an actual filter tool to accomplish this?

Cheers

Lisa

[KaneG · Accepted solution]

I think you have made your job harder than it needs to be ;).... step back as the True False is the only result of the Filter tool

[REPORTING PERIOD]=[MONTH] should do what you're after

Note: You could use a IF/THEN formula in a filter tool but it will be evaluating whether the data matches the True or false condition. If you think about the following data:

Field1 Field2
RED 1
BLUE 2
BLUE 1
RED 2

Then the following filter condition would work to split the data 

[Field2] = (IF [Field1]="BLUE" THEN 1 Else 2 ENDIF)

[Lisa_M]

I always have to look for the hard way to do things!  Thanks Kane!