Fieldname<> multiple values question

[stevelord]

Hi, as part of an if statement, I want to include Gender<>'M','m','Male','male' instead of Gender<>M AND Gender<>m etcetera.

Can someone show me the correct syntax to put that piece in a script if statement?  I'm sure there must be some symbols needed to separate that list from the true/false parts following the last two commas of an if statement.   Thanks!

[rustyfishbones]

You could use the REPLACE function in the Script to REPLACE all Values like m,M,male,Male etc.. with MALE

Here is a video on how to use the REPLACE Function

https://www.youtube.com/watch?v=ghQfeJ6l7IY

And here is a sample code

LOAD....

REPLACE(REPLACE(REPLACE(REPLACE(Gender,'M','MALE'),'m','MALE'),'Male','MALE'),'male','MALE') as Gender

FROM....

[jeffmartins]

Hi Steve Lord,

You can try the mixmatch function to solve this.

Example:

if(not MixMatch(Gender,'Male','M' ), 'F', 'M') as Gender

But, if you're only intended to get the first letter of the Gender field the following solution is simpler:

upper(left(Gender,1)) as Gender

Hope it helps you.

Regards

[marcus_sommer]

By a large number of translations could it be easier to use pick/match or mapping:

pick(match(upper(Gender), 'M', 'MALE'), 'male')

mapping ( load-befehl |select-befehl )

Mapping1:

MAPPING LOAD * INLINE [
x,y
US,USA
U.S.,USA
America,USA
Amerika,USA];

- Marcus

[stevelord]

This is good stuff, but I will clarify.  I actually have a statement looking to see if folks had a testvalue in testname fields with male or female in the name, and using that to populate male or female if missing in the Gender field.  So if a person had a value in body fat male field, but not value in gender, the statement would see the value in the first field and put male in the gender field.  (The gender field is used in alot of gender-specific threshold computations and I am trying to populate it more to get more individuals into those aggregates.)

I actually realized that if someone had already put something in the Gender field, I don't really want my formula to overwrite it, so I canned that down to gender=isnull() orgender=''U'.  However, I have strings of ORs I'd like to consolidate below.

Anything for '0<fieldname1, fieldname2, fieldname3'?   (Same question, more fields.)

if((([Body Fat (Male)]>0 OR [Waist-To-Hip Ratio (Male)]>0 OR [Waist Circumference (Male)]>0) AND (Gender=IsNull() OR Gender='U'), Dual('Male',1),

(([Body Fat (Female)]>0 OR [Waist-To-Hip Ratio (Female)]>0 OR [Waist Circumference (Female)]>0) AND (Gender=IsNull() OR Gender='U'), Dual('Female',2)) as Gender

Logic of the if statements is just easier for me to grasp and seems cleaner for grabbing data from here there and everywhere.

PS> Something's off in the paranthesis above, but I'll get that sorted eventually..!

[stevelord]

I was messing around with another script that had ApplyMap in it and realize I may need to do an applymap here afterall, because the Gender field is on one table and the testnames are in another table. So I’d make an MappingLoad with the male/female testnames mapping to ‘Male’ and ‘Female’, then do an ApplyMap in the table with the Gender field.

Going to give helpful points all over here and come back later with a correct answer to whichever one of these is closest to what I actually get to work right in the script. ☺

[stevelord]

Update 6/10/2013: This was close but I found a gap, so will post something else if/when I get a full solution.

Here is the final answer that did a clean job working!  Zattributes and Biometrics are the source tables.  I followed them with a couple of mapping load tables,  and finished with one more resident load, a fantastic dual if applymap sequence, and a drop table.

ZAttributes:

LOAD TEXT(UPPER(UserId)) as UserId,

     AttributeName,

     AttributeType,

     if(AttributeName='Gender', AttributeValue) as ZGender

FROM

(qvd) Where AttributeName='Gender';

Biometrics:

LOAD TEXT(UPPER(UserId)) as UserId,

     Source,

     TestName,

     TestValue,

     Date(Floor(TestDate)) as TestDate,

     Year(TestDate) as Year,

     ImportedDate,

     Biometric_Season,

     if(TestName='Systolic',TestValue,Null()) as Systolic,

     if(TestName='Diastolic',TestValue,Null()) as Diastolic,

     if(TestName='BMI',TestValue) as BMI,

     if(TestName='Fasting',TestValue,Null()) as Fasting,

     if(TestName='NonFasting' OR TestName='Non-Fasting',TestValue,Null()) as NonFasting,

     if(TestName='Triglycerides',TestValue,Null()) as Triglycerides,

     if(TestName='LDL',TestValue,Null()) as LDL,

     if(TestName='HDL',TestValue,Null()) as HDL,

     if(TestName='Cholesterol Ratio',TestValue,Null()) as [Cholesterol Ratio],

     if(TestName='Total Cholesterol',TestValue,Null()) as [Total Cholesterol],

     if(TestName='Waist Girth (Male)' OR TestName='Waist Circumference (Male)',TestValue,Null()) as [Waist Circumference (Male)],

     if(TestName='Waist Girth (Female)' OR TestName='Waist Circumference (Female)',TestValue,Null()) as [Waist Circumference (Female)],

     if(TestName='Body Fat (Male)',TestValue,Null()) as [Body Fat (Male)],

     if(TestName='Body Fat (Female)',TestValue,Null()) as [Body Fat (Female)],

     if(TestName='Waist-To-Hip Ratio (Male)',TestValue,Null()) as [Waist-To-Hip Ratio (Male)],

     if(TestName='Waist-To-Hip Ratio (Female)',TestValue,Null()) as [Waist-To-Hip Ratio (Female)],

     if(TestName='Weight',TestValue,Null()) as Weight

FROM

(qvd)

WHERE (Year(TestDate)>2007 AND TestDate<=NOW()) AND

(TestName='Systolic' OR TestName='Diastolic' OR TestName='BMI'

OR TestName='Fasting' OR TestName='NonFasting' OR TestName='Non-Fasting'

OR TestName='Triglycerides' OR TestName='LDL' OR TestName='HDL'

OR TestName='Cholesterol Ratio' OR TestName='Total Cholesterol' OR TestName='Weight'

OR TestName='Waist Girth (Male)' OR TestName='Waist Girth (Female)'

OR TestName='Waist-To-Hip Ratio (Male)' OR TestName='Waist-To-Hip Ratio (Female)');

BiometricsGenderMaleMap:

Mapping LOAD

([Body Fat (Male)]>0 OR [Waist-To-Hip Ratio (Male)]>0 OR [Waist Circumference (Male)]>0), 'Male'

RESIDENT Biometrics;

BiometricsGenderFemaleMap:

Mapping LOAD

([Body Fat (Female)]>0 OR [Waist-To-Hip Ratio (Female)]>0 OR [Waist Circumference (Female)]>0), 'Female'

RESIDENT Biometrics;

Attributes:

LOAD *,

     if(ZGender='' or ZGender='U', applymap('BiometricsGenderMaleMap',1),

     if(ZGender='' or ZGender='U', applymap('BiometricsGenderFemaleMap',2),

     if(ZGender='M' or ZGender='m' or ZGender='Male', Dual('Male',1),

     if(ZGender='F' or ZGender='f' or ZGender='Female', Dual('Female',2),

     if(ZGender='' or ZGender='U', Dual('Not Specified',3)))))) as Gender

RESIDENT ZAttributes;

Drop Table ZAttributes;

Thanks, the helpful stuff pointed me in the right way and got me thinking bigger!

So correct answer was I can't Gender<>a,b,c,d to shortcut or or or, and had to do lots of  mapping instead.  Your answers fit my problem better than my question.

-Steve