Three if match statmenst in a row??

[#1]

Hi,

I want to write a if match statement for three different values, the compiler does not giva any error but the table only shows Smoker after its created so the last values work. What am I doing wrong? Any tips?

if(Value=1 and (Match(CalculationIdentifier, 37,38)),'Smoker and Snuff user'),IF(Value=1 and (Match(CalculationIdentifier, 37)), 'Snuff user'),IF(Value=1 and (Match(CalculationIdentifier,38)), 'Smoker') as SmokeAndSnuffStatus,

[swuehl]

I think this code is creating three fields:

if(Value=1 and (Match(CalculationIdentifier, 37,38)),'Smoker and Snuff user'), // field named like the expression

IF(Value=1 and (Match(CalculationIdentifier, 37)), 'Snuff user'), // field named like the expression

IF(Value=1 and (Match(CalculationIdentifier,38)), 'Smoker') as SmokeAndSnuffStatus,

edit:

And your first line should evaluate to 'Smoker OR Snuff user' since the match will not test for and condition, but return a value >0 if CalculationIdentifier is 37 OR 38.

[Anonymous]

if you are getting Smoker only, its mean you are having CalculationIdentifier with 38 only.

Check the data, do you have 37?

[#4]

Ok it weird because I thought when setting 'as' I create a table (files as I called it) how do I create one table with three different values like based on the if statement so SmokeAndSnuffStatus table would contain values ''Smoker and Snuff user' , 'snuff user' and 'smoker'?

[#5]

Yes I have 37, and values for that

[Anonymous]

try this one:

You have closed brackets after every value like 'Snuff user') which is wrong:

try this:

if((Value=1 and (Match(CalculationIdentifier, 37,38))),'Smoker and Snuff user',

IF((Value=1 and (Match(CalculationIdentifier, 37))), 'Snuff user',

IF((Value=1 and (Match(CalculationIdentifier,38))), 'Smoker')))as SmokeAndSnuffStatus,

[swuehl · Accepted solution]

This expression is evaluated per input record, how would you determine Smoker and Snuff based on a single CalculationIdentifier record? Maybe it might help if you post some sample data.

This should create a field with Snuff / Smoker values:

IF(Value=1 and (Match(CalculationIdentifier, 37)), 'Snuff user',

IF(Value=1 and (Match(CalculationIdentifier,38)), 'Smoker')) as SmokeAndSnuffStatus,

Note the changed brackets.

[maxgro]

this is correct from a syntax point of view but I think you can never get the bold result because of the underlined if condition

z:

load

  *,

  if(Value=1 and Match(CalculationIdentifier, 37, 38),'Smoker and Snuff user',

  IF(Value=1 and Match(CalculationIdentifier, 37), 'Snuff user',

  IF(Value=1 and Match(CalculationIdentifier,38), 'Smoker',

  'other'

  ))) as SmokeAndSnuffStatus

inline [

Value, CalculationIdentifier

0,37

0,38

0,10

1,37

1,38

1,10

];

[#9]

No the smoke and snuff user shows ant not the snuff users nor smokers. And I have both values in the data

[#10]

Did not work sorry I only get the 'Smoker and Snuff user' and other not 'snuff user' or smoker. And I have people with only snuff and smoke user value 37 and 38 seperatly any other ides? thx