topic Re: nested If optimization in QlikView

nested If optimization

fonsosurfer — Wed, 25 Nov 2020 16:16:04 GMT

Hi, Goog morning!

I'm trying to optimize an IF statement that I have in a Load. This is my IF expression:

LOAD

Grupo1,

Original1,

if(Original1 <> 'Anything', // = 'N/A',

If (substringcount (upper(Grupo1),'N1') > 0, 'N1',

If (substringcount (upper(Grupo1),'N2')>0 or substringcount (upper(Grupo1),'N3')>0 or substringcount (upper(Grupo1),'N4')>0, 'Nsw',

If (substringcount (upper(Grupo1),'OLC') > 0 or substringcount (upper(Grupo1),'LCO') > 0 or substringcount (upper(Grupo1),'IMPL') > 0, 'Implantación' ,

If (substringcount (upper(Grupo1),'GESTION DE CAMBIOS') > 0 or substringcount (upper(Grupo1),'GESTIÓN DE CAMBIOS') > 0

or substringcount (upper(Grupo1),'CHANGE MANAGEMENT') > 0 or substringcount (upper(Grupo1),'SUPPORT & DEPLOYMENT') > 0

or substringcount (upper(Grupo1),'GDC') > 0, 'GGCC' ,

'Otros'

)

,Original1 ) as Nivel1

RESIDENT

TABLA1;

Any suggestion?

Thanks a lot!!!

Re: nested If optimization

vishsaggi — Thu, 27 Jul 2017 18:26:46 GMT

What is the problem with your current IF is it taking lot of time?

Re: nested If optimization

ziadm — Thu, 27 Jul 2017 18:41:26 GMT

Try to Use ApplyMap instead .. if you are able to map Grupo1 Field to

Nsw

Implantación

This would be more efficient and do not have to change the code

Re: nested If optimization

effinty2112 — Thu, 27 Jul 2017 19:16:19 GMT

Hi Alfonso,

You could try this to see if it's quicker:

LOAD

Grupo1,

Original1,

if(Original1 <> 'Anything', // = 'N/A',

Pick(1+Wildmatch(upper(Grupo1),'*N1*','*N2*','*N3*','*N4*','*OLC*','*LCO*','*IMPL*','*GESTION DE CAMBIOS*','*GESTIÓN DE CAMBIOS*','*CHANGE MANAGEMENT*','*SUPPORT & DEPLOYMENT*','GGCC'),

'Otros','N1','Nsw','Nsw','Nsw','Nsw', 'Implantación', 'Implantación', 'Implantación', 'GGCC', 'GGCC', 'GGCC', 'GGCC', 'GGCC')

)

,Original1 ) as Nivel1

RESIDENT

TABLA1;

Cheers

Andrew

Re: nested If optimization

Kushal_Chawda — Fri, 28 Jul 2017 07:42:15 GMT

If you can share the sample data, it will be helpful to give you more optimized way to do this

Re: nested If optimization

fonsosurfer — Mon, 31 Jul 2017 06:27:42 GMT

I think so. Im having a message error: "allocated memory exceeded". Probably is because de Ifs