Pick statement around an if statement

[Anonymous]

I have the following if statement.

If ( wildmatch (SALES_PERSON, 'JOHN*', 'ALLEN*'), 'EUROPE',  // IF SALES_PERSON EQUAL TO JOHN OR ALLEN THEN                                                                                                                            RETURN EUROPE

REGION  as REGION_NEW                                                            // OTHERWISE RETURN 'REGION' as REGION_NEW

I want to add further logic to this statement which says if REGION is equal to AMERICA then re-label this as USA. Something along the lines of :

pick(Match(REGION, 'AMERICA'),'USA','REGION')  // IF REGION EQUAL TO AMERICA THEN LABEL AS USA OTHERWISE                                                                                                  RETURN REGION

I cant get both bits of logic to work correctly in a single statement - think my syntax might be out somewhere. Any assistance appreciated.

Cheers

[sunny_talwar · Accepted solution]

May be this

Table:

LOAD SALES_PERSON,

Pick(Match(

If(WildMatch(SALES_PERSON, 'JOHN*', 'ALLEN*'), 'EUROPE',

If(WildMatch(SALES_PERSON, 'BOB*'), 'BRAZIL',

If(WildMatch(SALES_PERSON, 'TOBY*'), 'GERMANY',

If(WildMatch(SALES_PERSON, 'ANIL*'), 'INDIA', REGION)))), 'AMERICA') + 1,

If(WildMatch(SALES_PERSON, 'JOHN*', 'ALLEN*'), 'EUROPE',

If(WildMatch(SALES_PERSON, 'BOB*'), 'BRAZIL',

If(WildMatch(SALES_PERSON, 'TOBY*'), 'GERMANY',

If(WildMatch(SALES_PERSON, 'ANIL*'), 'INDIA', REGION)))), 'USA') as REGION;

LOAD * INLINE [

    SALES_PERSON, REGION

    JOHN, USA

    ALLEN, EUROPE

    RAVI, AMERICA

    BOB, BRAZIL

    TOBY,

    ANIL,

    RAKESH, AMERICA

    RAJIV, UK

];

[Anonymous]

Thanks Sunny, it worked great.