Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
Where are you trying this? In expression Label or Where?
In script
May be this
Pick(Match(If(WildMatch(SALES_PERSON, 'JOHN*', 'ALLEN*'), 'EUROPE', REGION), 'AMERICA') + 1, REGION, 'USA') as REGION
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
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
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
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
Can you share a sample to see the issue?
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