Genii Weblog
Top that, Rocky!
Thu 16 Dec 2004, 01:46 PM
Tweetby Ben Langhinrichs
I am working on this year's Lotusphere sessions database, and had a request for sorting the speakers by last name. Since the Speaker field is stored as a multi-value list, such as "Thomas Duff":"Joe Litton", this would seem easily handled by a categorized column with a formula such as
Original Formula
@RightBack(Speaker; " ")+", "+@LeftBack(Speaker; " ")
which works great until you see session AD218, which is being presented by "Cees van der Woude" and "Peter Janzen". Well, we don't want the "Cees van der Woude" to be returned as "Woude, Cees van der" because that would be both inaccurate and culturally ignorant. So, how do we handle this? Also, how do we handle STR115, which has a speaker named "There will be a second speaker from my group, TBD. x"? How do we handle "TBD", which several sessions still have? If this were a single value, the formula could handle these exceptions more easily, but the multi-value list makes it very hard...
Except for @Transform, which Rocky Oliver wrote about this week, which does indeed allow us to act as if this is a single value instead of a list. Let's see if we can come up with a formula for our categorized column that handles these exceptions cleanly.
After some playing around, I came up with the following, which may look complicated, but which basically makes anything with the string "TBD" in it into a category "TBD" and then looks for the a match for the second word to see if it starts with a lowercase letter, and so on.
Special Formula #1
@Transform(Speaker; "S";
@If(S = "TBD":"" | @Contains(S; "TBD"); "TBD";
@Matches(@Left(@Word (S; " "; 2); 1); "{a-z}");
@Word (S; " "; 2)+@RightBack(S; @Word (S; " "; 2))+", "+@LeftBack(S; @Word (S; " "; 2));
@Matches(@Left(@Word (S; " "; 3); 1); "{a-z}");
@Word (S; " "; 3)+@RightBack(S; @Word (S; " "; 3))+", "+@LeftBack(S; @Word (S; " "; 3));
@RightBack(S; " ")+", "+@LeftBack(S; " ")));
After a bit more looking, I realized I wasn't using the @Matches efficiently, so I changed the logic to match the whole string by adding an asterisk at the end:
Special Formula #2
@Transform(Speaker; "S";
@If(S = "TBD":"" | @Contains(S; "TBD"); "TBD";
@Matches(@Word (S; " "; 2); "{a-z}");
@Word (S; " "; 2)+@RightBack(S; @Word (S; " "; 2))+", "+@LeftBack(S; @Word (S; " "; 2));
@Matches(@Word (S; " "; 3); "{a-z}");
@Word (S; " "; 3)+@RightBack(S; @Word (S; " "; 3))+", "+@LeftBack(S; @Word (S; " "; 3));
@RightBack(S; " ")+", "+@LeftBack(S; " ")));
Still, this didn't seem very elegant, and wouldn't handle Cindy Lou Who van der Pelt, for example. Then, inspiration hit. Why not use a nested @Tranform? (Yes, Rocky, I got carried away)
Final Formula
@Transform(Speaker; "S";
@If(S = "TBD":"" | @Contains(S; "TBD"); "TBD";
@Do(R:=@Transform(@Explode(S; " "); "W";
@If(@Matches(W; "{a-z}*"); W+@RightBack(S; W)+", "+@LeftBack(S; W); @Nothing));
@If (@Trim(R) != ""; @Trim(R)[1]; @RightBack(S; " ")+", "+@LeftBack(S; " ")))));
Top that, Rocky!
OK, Rocky, where is your example with nested @Transform's? Do you have a more elegant and powerful formula to show?
Copyright © 2004 Genii Software Ltd.
What has been said:
252.1. Rob McDonagh (12/17/2004 06:04 AM)
Am I the only one who thinks this challenge could be turned into a really cool segment of Rocky's formula session? Heck, you could make a whole session out of programming challenges between various experts. Give them a problem, lock them in a room for an hour, and then have them present the solutions to an audience. Damien could judge who did better. That would be fun to see!
252.2. Ben Langhinrichs (12/17/2004 06:17 AM)
Cool. We probably couldn't do it in the session itself, but Penumbra will have a suite, and perhaps we could schedule it there for later.
252.3. Julian Robichaux (12/17/2004 03:11 PM)
How about:
@MidasConvertSpeakerName(Speaker)
:-)
- Julian