How do I write a script for this...?

[Anonymous]

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~!

[#2]

hi

try using IF Function

instead of WHEN

regards

Gernan

[#3]

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;

[Anonymous]

Thanks, Wes.  But what if Field1 is never a Null, it's always ' ' instead of Null...?

[#5 · Accepted solution]

You can check for the space in your IF statement:

if(Field1<>' ', Field1, Field2)

[Anonymous]

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.

[Anonymous]

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'.