topic Re: Nested -if- optimization in QlikView

Nested -if- optimization

Fri, 30 Dec 2011 02:39:24 GMT

Hi guys,

I have this nested if and i wonder if there's a much simpler way to code this:

     IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=China') > 1, 'China',
        IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=India') > 1, 'India',
           IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Singapore') > 1, 'Singapore',
              IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Thailand') > 1, 'Thailand',
                 IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Vietnam') > 1, 'Vietnam',
                    IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Japan') > 1, 'Japan',
                       IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Korea') > 1, 'Korea',
                          IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Malaysia') > 1, 'Malaysia',
                             IF(INDEX(UserDN,'OU=Lake Mary Office') > 1, 'Lake Mary Office',
                                IF(INDEX(UserDN,'OU=Germany Office') > 1, 'Germany Office',
                                   IF(INDEX(UserDN,'OU=PA Office') > 1, 'PA Office',
                                     'NO COUNTRY'))))))))))) AS UserCountry,

It basically check the presense of 'OU=Countries' + OU=[country name] then if exist it will assign the correct value for UserCountry.\

Regards,

~skip~

Re: Nested -if- optimization

sridhar240784 — Fri, 30 Dec 2011 05:22:39 GMT

Hi,

One way i could think of now is using Pick() & WildMatch() functions.

Load Country,

Region,

Pick(WildMatch(Country,1,2,3),

Pick(WildMatch(Region,'*India*','*china*','*sing*'),'India','China','Singapore'),

Pick(WildMatch(Region,'*thai*','*bankok*','*malasiya*'),'Thailand','Bankok','Malasiya'),

Pick(WildMatch(Region,'*china*','*uk*'),'Srilanka','UK')) as Region_Nor ;

LOAD * INLINE [

Country, Region

1, India123

1, china

1, singapore

2, thai123

2, bankok123

2, malasiya123

3, china123

3, uk123

];

Hope this helps you.

-Sridhar

Re: Nested -if- optimization

rwunderlich — Fri, 30 Dec 2011 23:40:19 GMT

First suggestion, there is no need to ident for a if/else structure. It's easier to read and add to without the indentation.

IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=China') > 1, 'China',

IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=India') > 1, 'India',

IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Singapore') > 1, 'Singapore',

IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Thailand') > 1, 'Thailand',

IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Vietnam') > 1, 'Vietnam',

IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Japan') > 1, 'Japan',

IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Korea') > 1, 'Korea',

IF(INDEX(UserDN,'OU=Countries') > 1 AND INDEX(UserDN,'OU=Malaysia') > 1, 'Malaysia',

IF(INDEX(UserDN,'OU=Lake Mary Office') > 1, 'Lake Mary Office',

IF(INDEX(UserDN,'OU=Germany Office') > 1, 'Germany Office',

IF(INDEX(UserDN,'OU=PA Office') > 1, 'PA Office',

'NO COUNTRY'

))))))))))) AS UserCountry

I only use indentation to indicate a then/if, which you could use to eliminate repeating the OU=Countries test.

IF(INDEX(UserDN,'OU=Countries') > 1,

IF(INDEX(UserDN,'OU=China') > 1, 'China',

IF(INDEX(UserDN,'OU=India') > 1, 'India',

IF(INDEX(UserDN,'OU=Singapore') > 1, 'Singapore',

IF(INDEX(UserDN,'OU=Thailand') > 1, 'Thailand',

IF(INDEX(UserDN,'OU=Vietnam') > 1, 'Vietnam',

IF(INDEX(UserDN,'OU=Japan') > 1, 'Japan',

IF(INDEX(UserDN,'OU=Korea') > 1, 'Korea',

IF(INDEX(UserDN,'OU=Malaysia') > 1, 'Malaysia'

))))))))

IF(INDEX(UserDN,'OU=Lake Mary Office') > 1, 'Lake Mary Office',

IF(INDEX(UserDN,'OU=Germany Office') > 1, 'Germany Office',

IF(INDEX(UserDN,'OU=PA Office') > 1, 'PA Office',

'NO COUNTRY'

)))) AS UserCountry

Assuming the country you want is always the second OU in the string, you can further simplify to:

IF(INDEX(UserDN,'OU=Countries') > 1, TextBetween(UserDN, 'OU=', ',', 2),

IF(INDEX(UserDN,'OU=Lake Mary Office') > 1, 'Lake Mary Office',

IF(INDEX(UserDN,'OU=Germany Office') > 1, 'Germany Office',

IF(INDEX(UserDN,'OU=PA Office') > 1, 'PA Office',

'NO COUNTRY'

)))) AS UserCountry

You may also be able to use TextBetween for the remaining offices -- depending on your data.

Consider using LIKE instead of INDEX if your data pattern will support it. I find LIKE easier to read and code.

IF(UserDN like '*OU=Countries*'

-Rob

http://robwunderlich.com

Nested -if- optimization

Tue, 03 Jan 2012 02:01:47 GMT

Thanks again Rob... You are simply the best.