topic Re: Multiple IF replace and Alternate Function Needed in QlikView

Multiple IF replace and Alternate Function Needed

Gautam — Wed, 01 May 2019 10:47:00 GMT

Hi All,

I have around 50 if Conditions on the same Field like salesman 1 = 'XXX' then name = ÝYYY', like below I have to check for 50 values while loading the data. is there any alternate way to replace IF with another function?

LOAD * ,
if([Salesman 1]='MKAHN','Outside',

if([Salesman 1]='TZERB','Outside',

if([Salesman 1]='KNIBL','Outside',

if([Salesman 1]='ERAMO','Outside',

if([Salesman 1]='MNOBL','Outside',Salesmantype_old ))))) as Salesmantype;

Re: Multiple IF replace and Alternate Function Needed

Anil_Babu_Samineni — Wed, 01 May 2019 11:10:48 GMT

We can do many ways?

1) Like what you done

2) Replace function with every time - Not recommended

3) Pick Match as follows - Recommended

Pick(Match([Salesman 1], 'MKAHN', 'TZERB', 'KNIBL', 'ERAMO', 'MNOBL'), 'Outside',Salesmantype_old) as Salesmantype

4) Mapping Table for all 50 rows with same name - Recommended

Re: Multiple IF replace and Alternate Function Needed

sunny_talwar — Wed, 01 May 2019 11:33:33 GMT

I would suggest to use ApplyMap() function with Mapping Load

Re: Multiple IF replace and Alternate Function Needed

kushalthakral — Wed, 01 May 2019 11:33:49 GMT

Hi
Pick function will not work the way you have specified needs to be write Outside many times

Thanks
Kushal

Re: Multiple IF replace and Alternate Function Needed

Anil_Babu_Samineni — Wed, 01 May 2019 11:46:26 GMT

@kushalthakral Try and let us know where it is not working 🙂

Re: Multiple IF replace and Alternate Function Needed

sunny_talwar — Wed, 01 May 2019 11:49:28 GMT

@Anil_Babu_Samineni I think what @kushalthakral is trying to say is that you need if instead of Pick to do this

If(Match([Salesman 1], 'MKAHN', 'TZERB', 'KNIBL', 'ERAMO', 'MNOBL'), 'Outside', Salesmantype_old) as Salesmantype

or you need this

Pick(Match([Salesman 1], 'MKAHN', 'TZERB', 'KNIBL', 'ERAMO', 'MNOBL') + 1, Salesmantype_old, 'Outside', 'Outside', 'Outside', 'Outside', 'Outside') as Salesmantype

Pick will need n+1 number of arguments for n number of arguments in Match.

Re: Multiple IF replace and Alternate Function Needed

Anil_Babu_Samineni — Wed, 01 May 2019 14:14:25 GMT

I agree with you @sunny_talwar and @kushalthakral