Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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....
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
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
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..!
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. ☺
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