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

[Anil_Babu_Samineni]

Where are you trying this? In expression Label or Where?

[Anonymous]

In script

[sunny_talwar]

May be this

Pick(Match(If(WildMatch(SALES_PERSON, 'JOHN*', 'ALLEN*'), 'EUROPE', REGION), 'AMERICA') + 1, REGION, 'USA') as REGION

[Anonymous]

Thanks Sunny. What if I had multiple statements ie :

if ( wildmatch (SALES_PERSON, 'JOHN*', 'ALLEN*'), 'EUROPE', 

if ( wildmatch (SALES_PERSON, 'BOB*'), 'BRAZIL',

REGION ))  as REGION_NEW                                                          

[sunny_talwar]

I would just add it after Match I guess

Pick(Match(

if ( wildmatch (SALES_PERSON, 'JOHN*', 'ALLEN*'), 'EUROPE',

if ( wildmatch (SALES_PERSON, 'BOB*'), 'BRAZIL',

REGION ))

, 'AMERICA') + 1, REGION, 'USA') as REGION

[jayanttibhe]

Avoid Multiple WildMatches ::

Pick(Match(

if ( wildmatch (SALES_PERSON, 'JOHN*', 'ALLEN*','BOB*'), 'EUROPE', 'EUROPE', 'BRAZIL'),REGION )

, 'AMERICA') + 1, REGION, 'USA') as REGION

*** Check brackets if closed prperly

[Anonymous]

Thanks Sunny. It looked to work well but the last statement doesnt seem to work correctly. If have 4 if statements to be precise as below :

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, REGION, 'USA') as REGION

[sunny_talwar]

Can you share a sample to see the issue?

[Anonymous]

Unfortunately not. The logic did work in terms of re-labelling the america to USA however the last if statement doesnt work which leads me to think the parantheses may be out somewhere