Logical equivalent IN operator in a Conditional Split Transformation

windthorstking

I am using a Conditional Split Transformation to find incorrect city listings in a specific list of zipcodes. I have the following condition:

LTRIM([PatientCity]) != 'Wichita Falls' && [PatientZip] IN '76301','76302','76305','76306','76307','76308','76309','76310')

I found the && which is the logical equivalent to AND. I now need a logical operator for IN.

Any ideas or approaches on how I may do this

Phil Brammer


No such thing.

You could try:

LTRIM([PatientCity]) != 'Wichita Falls' && ([PatientZip] == '76301' || [PatientZip] == '76302' || [PatientZip] == '76305' || ...etc...)

windthorstking

Thanks Phil, that appears to work.

Jamie Thomson

Phil Brammer wrote:

No such thing.

Really I guess not exactly but FINDSTRING() might work

Phil Brammer

Jamie Thomson wrote:

Phil Brammer wrote: No such thing. Really What about FINDSTRING()

Something like the following

FINDSTRING("12345,12346,12347",[PatientZip],1)

Jamie Thomson

Phil Brammer wrote:

Jamie Thomson wrote: Phil Brammer wrote: No such thing. Really What about FINDSTRING() Something like the following FINDSTRING("12345,12346,12347",[PatientZip],1)

Yeah, that's what I had in mind.

Phil Brammer

I guess it should work...... Obviously, I never thought about that...

windthorstking

Phil Brammer wrote:

No such thing. You could try: LTRIM([PatientCity]) != 'Wichita Falls' && ([PatientZip] == '76301' || [PatientZip] == '76302' || [PatientZip] == '76305' || ...etc...)

Phil - I ran into some issues using the above as some of the PatientZip and PatientCity fields were NULL which Boolean did not like very much. I have tried the following but don't quite have it right, can you suggest a better method to ensure NULL values do not fail the conditional split transformation task

LTRIM(ISNULL([PatientCity])) != 'Wichita Falls' && (ISNULL([PatientZip]) == '76301' || [PatientZip] == '76302' || [PatientZip] == '76305')

Phil Brammer

I'd use two derived columns. The first to test PatientZip for NULL and if so, replace it with a space, or something.

The second to do the above example, either using my first method, or the FINDSTRING method that Jamie and I talked about.