Nested -if- optimization

[#1]

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~

[sridhar240784]

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

[rwunderlich · Accepted solution]

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

[#4]

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