Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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~
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'
))))))))
,
)))) 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),
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
View solution in original post
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
Thanks again Rob... You are simply the best.
