Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two fields as below, and I'm trying to add a Field3 to identify the nature of Field1 and Field2. Field1 is the original product list, when Field1 is ' ', we look at Field2 to decide what to put in Field3.
Field1 Field2
'apple' ' '
'peach' ' '
'strawberry' ' '
'greenbean' ' '
' ' 'cabbage'
'wheat' ' '
'tomato'
'broccoli' 'broccoli'
'corn' ' '
: :
. .
then, I wanna add a Field3,
1.when Field1='apple','peach','strawberry' then Field3=fruit;
2.when Field1='greenbean','cabbage','broccoli','tomato' then Field3=veggie;
3. when Field1='wheat','corn' then Field3=grain.
4. when Field1=' ', go see Field2, follow the same rule.
So the completed table is like,
Field1 Field2 Field3
'apple' ' ' fruit
'peach' ' ' fruit
'strawberry' ' ' fruit
'greenbean' ' ' veggie
' ' 'cabbage' veggie
'wheat' ' ' grain
'tomato' veggie
'broccoli' 'broccoli' veggie
'corn' ' ' grain
: : :
. . .
Thanks, guys~!
You can check for the space in your IF statement:
if(Field1<>' ', Field1, Field2)
hi
try using IF Function
instead of WHEN
regards
Gernan
Hi Lynn,
You can try something similar to this if you have a small number of conditions as in your example above:
TableName:
LOAD
Field1,
Field2,
If( MixMatch(If(Field1<>Null(), Field1, Field2),'APPLE','PEACH','STRAWBERRY'), 'FRUIT',
If( MixMatch(If(Field1<>Null(), Field1, Field2),'greenbean','cabbage','broccoli','tomato'), Veggie,
'Grain')) as FoodType
FROM
DataSource;
Thanks, Wes. But what if Field1 is never a Null, it's always ' ' instead of Null...?
You can check for the space in your IF statement:
if(Field1<>' ', Field1, Field2)
if you have a list of fruits, veggies, grains in a separate table, then you can use apply map too -
so mapping load of name, type fields from the list table (apple, fruit; wheat, grain; etc.) then
map1:
Mapping LOAD * Inline [
name, type
apple, fruit
banana, fruit
peach, fruit
broccoli, veggie
cabbage, veggie
tomato, veggie
wheat, grain
corn, grain
];
base1:
load *,
ApplyMap('map1', if(len(Field2)>0, Field2, Field1)) as F3
Resident base;
where "base" is the first table in your example with Field1 and Field2. If you have a space as value (' '), then you can use 1 instead of 0 in the len comparison.
Hi Lynn,
You can use a mapping table and create all your lookup values like this:
mapMasterList:
Mapping Load * Inline [
Input, Output
apple, fruit
peach, fruit
strawberry, fruit
greenbean, veggie
cabbage, veggie
wheat, grain
tomato, veggie
broccoli, veggie
corn, grain
];
Load
Field1,
Field2,
Applymap('mapMasterList', if(len(PurgeChar(Field1,chr(32)))=0, Field2, Field1), 'NA') as Field3
From XYZ;
The Applymap function will see if we remove all spaces from Field1, is it longer than 0 characters? If not, then use Field2. If Field1 actually has a value, use Field1. This value is then mapped against the mapping table to return the output value for Field3. If no match is found, the Applymap function will return 'NA'.