topic Re: Pick statement around an if statement in QlikView

Pick statement around an if statement

Anonymous — Thu, 17 Aug 2017 15:29:28 GMT

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

Re: Pick statement around an if statement

Anil_Babu_Samineni — Thu, 17 Aug 2017 15:42:48 GMT

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

Re: Pick statement around an if statement

Anonymous — Thu, 17 Aug 2017 15:43:52 GMT

In script

Re: Pick statement around an if statement

sunny_talwar — Thu, 17 Aug 2017 15:47:45 GMT

May be this

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

Re: Pick statement around an if statement

Anonymous — Thu, 17 Aug 2017 15:59:09 GMT

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

Re: Pick statement around an if statement

sunny_talwar — Thu, 17 Aug 2017 16:05:11 GMT

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

Re: Pick statement around an if statement

jayanttibhe — Thu, 17 Aug 2017 16:18:27 GMT

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

Re: Pick statement around an if statement

Anonymous — Thu, 17 Aug 2017 17:08:11 GMT

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

Re: Pick statement around an if statement

sunny_talwar — Thu, 17 Aug 2017 17:10:18 GMT

Can you share a sample to see the issue?

Re: Pick statement around an if statement

Anonymous — Thu, 17 Aug 2017 17:13:41 GMT

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

Re: Pick statement around an if statement

sunny_talwar — Thu, 17 Aug 2017 17:15:45 GMT

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

];

Re: Pick statement around an if statement

Anonymous — Fri, 18 Aug 2017 11:48:03 GMT

Thanks Sunny, it worked great.