Replacing values in a string

I received a request for help in a comment on one of my blog posts.

I don’t normally offer a helpdesk service, but I found the problem both interesting and challenging, so I thought I’d give it a go.

The question was:

“Hope you could help me here. I want to sort out free format text object which contains keywords. For example,

aaa/bbb/blue
ccc/ddd/yellow
eee/fff/red
ggg/hhh/xyz

Find “blue” from the object and replace the entire object with “azul”, find “yellow” then replace with “amarillo”, “red” into “rojo”. When neither “blue”, ”yellow” nor ”red” was found, replace the object with “not found”. So in the above example, “ggg/hhh/xyz” will be displayed as “not found”.

Thanks in advance for your assistance.”

Not being familiar with the entire data set or possible range of values, I need to make a few assumptions.

I assume you want to see the following output:

aaa/bbb/azul
ccc/ddd/amarillo
eee/fff/rojo
ggg/hhh/not found

I also assume that the position of the second forward slash may change.

If my assumptions are correct, we can move forward. If not, let me know!

When faced with such a problem, I like to break it down into logical steps, building up the formula until I get the result I’m after.

Now I’m assuming that you

For those that want to follow, I created a sample data file in Excel:

Excel Input

I then created a report using the Excel file.

The challenge is to replace the text after the second forward slash with another value depending on its content.

So lets start.

Using the Pos() function, you can determine the position of the first forward slash:

Output1

This gives us 4.

We need to find the position of the second slash, so we create a new string using the output from the function Pos(<F1>,”/”). To do this we use substr().

Substr allows us to take a part of string. To use this we pass in the data value, its start position and the length of the string to return.

For our example, we will use the following formula:

SubStr(<F1> ,Pos(<F1> ,”/”)+1 ,10)

If you are using Web Intelligence, the following formula will work:

SubStr([F1];Pos([F1];”/”)+1;10)

With this formula we are saying:

Return a substring of <F1>, stating in position 5, and bring back 10 characters.

Notice I said position 5 (we added +1). The reason for this is that we want to bring back the string after the first forward slash.

We end up with the following results:

Output2

Now we have a new string to work with, we need to find out the position of the forward slash in this string. We use:

Pos(SubStr(<F1> ,Pos(<F1> ,”/”)+1 ,10) ,”/”)

If you are using Web Intelligence, the following formula will work:

Pos(Substr([F1];Pos([F1] ;”/”)+1;10);”/”)

If we put these two formulas together, our result should be everything after the second forward slash:

Output3
If you are using Web Intelligence, the following formula will work:

Substr(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;Pos(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;”/”)+1 ;10)

We now have a formula to determine the character string after the second forward slash.
We can now start work on the real solution. We want to check for three possible values. If none of these values are found, we return a fourth ‘catch all’ value.

To perform this check, we use an If() function together with the Else command.

We also make use of the Replace() function within the statement to swap out the relevant values.

So we end up with the following formula:

If(SubStr(SubStr(<F1> ,Pos(<F1>,”/”)+1 ,10) ,Pos(SubStr(<F1> ,Pos(<F1> ,”/”)+1 ,10) ,”/”)+1 ,10)=”blue”) Then Replace(<F1> ,”blue” ,”azul”) Else
(If(SubStr(SubStr(<F1>,Pos( <F1>,”/”)+1 ,10) ,Pos(SubStr(<F1> ,Pos( <F1>,”/”)+1 ,10) ,”/”)+1 ,10)=”yellow”) Then Replace(<F1> ,”yellow” ,”amarillo”) Else
(If(SubStr(SubStr(<F1> ,Pos( <F1>,”/”)+1 ,10) ,Pos(SubStr(<F1> ,Pos( <F1>,”/”)+1 ,10) ,”/”)+1 ,10)=”red”) Then Replace(<F1> ,”red” ,”rojo”) Else
“not found”))

If you are using Web Intelligence, the following formula will work:

If(Substr(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;Pos(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;”/”)+1 ;10)=”blue”; Replace([F1] ;”blue” ;”azul”); (If(Substr(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;Pos(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;”/”)+1 ;10)=”yellow”; Replace([F1] ;”yellow” ;”amarillo”); (If(Substr(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;Pos(Substr([F1] ;Pos([F1] ;”/”)+1 ;10) ;”/”)+1 ;10)=”red”; Replace([F1] ;”red” ;”rojo”); “not found”)))))

Output4

Note: If you don’t want to see the whole string and simply want “azul”, “amarillo”, “rojo” or “not found” displayed, then change the Replace command slightly as follows:

Replace(<F1> ,<F1> ,”azul”) 

or in Web Intelligence:

Replace([F1];[F1];”azul”) 

So we have ended up with quite a long formula composed of several functions:

If()
Substr()
Pos()
Replace()

Remember:

We couldn’t just do a Replace(), as there are multiple values to replace.

We couldn’t use an If() and a Replace() function, as the data set and length is not known in advance. The position of the second forward slash may change.

Therefore we used the Pos() function to find the position of the second slash, and use the If, Substr and Replace functions to replace the contents following that second slash.

I hope you found this post helpful. If you have any comments, do let me know.

Advertisements

How to display the previous working day in Web Intelligence

Through the use of the DayName function, we can display the day name of the week for a particular date.

Today is Monday 13th February 2012.

What would be the result of creating the following formula in WebIntelligence?

=DayName(CurrentDate())

The answer is Monday. Today is Monday 13th February 2012.

Now, if I wanted to display the previous working day, I couldn’t use the RelativeDate function on it’s own. Remember we are looking for previous working day and not previous day.

In order to do this, we need to check the current day and determine if the previous day is a weekend. If it is, we need to adjust the output accordingly.

We can develop this logic within WebIntelligence using a nested IF statement.

Let’s work through an example:

Create a new WebIntelligence report. If you have a CurrentDate object in your universe, we can use that. If you don’t, then bring back any object (preferably one that doesn’t bring back many rows – we don’t actually want to use that value, but we do want to end up with a table).

If you do not have a CurrentDate object, create the following formula in WebIntelligence:

=CurrentDate()

Note that the format of the date value returned depends on how the universe object has been set up or how your WebIntelligence setup is configured. In some cases it may be formatted as dd-Mmm-yyy or dd/mm/yyyy. If you want to use a specific format and you are using the =CurrentDate() formula in WebIntelligence, you can format the date using:

=FormatDate(CurrentDate();”dd-Mmm-yyyy”)

For the purposes of this article however, we will be using the default format.

Add a second column to your table. We will call this column ‘Current Day Name’ and we will use the DayName function to return the day of the week:

=DayName(CurrentDate())

Now to get the previous working date.

We need to check the value (Day Name) of the current date. If it is a Tuesday, Wednesday, Thursday, Friday or Saturday, we will be fine using the RelativeDate function, as the previous day to the above listed days is a working day. (I’m ignoring Bank Holidays, Festive Days, etc. That’s for a future topic!)

However, if we find that the current Day Name is a Sunday or a Monday, then using RelativeDate is going to retrieve a weekend day. We need to handle these two scenarios using a nested IF statement.

The statement is built up as follows:

If the current day name is a Monday, then subtract three days from the current date
If the current day name is a Sunday, then subtract two days from the current date

The formula is made up as follows:

=If(DayName(CurrentDate())=”Monday”;RelativeDate(CurrentDate();-3);If(DayName(CurrentDate())=”Sunday”;RelativeDate(CurrentDate();-2);RelativeDate(CurrentDate();-1)))

Lets break down this formula.

An IF expression has three components:

If(boolean_expr;value if true;value if false)

The boolean_expr part is the statement that we test.

In our case, we want to check if the CurrentDate() is a Monday.

If it is true that it is a Monday, we want to use the RelativeDate function to subtract 3 days from the CurrentDate().

If it is false, i.e. the current date is not a Monday, we need to perform a further check. This requires a second IF statement to perform this second check.

The second check asks if the CurrentDate() is a Sunday.

If it is true that it is a Sunday, we want to use the RelativeDate function to subtract 2 days from the CurrentDate().

If it is false, i.e. the current date is not a Sunday, and we know its not a Monday as we checked for that previously, then we know that the day is a Tuesday, Wednesday, Thursday, Friday or Saturday and hence we can safely use RelativeDate with a value of -1 to give us the previous working day.

The formula above displays the result in a date format. If we wanted to display the result as a Day Name only, we can tweak the formula a little to incorporate the DayName() function:

=If(DayName(CurrentDate())=”Monday”;DayName(RelativeDate(CurrentDate();-3));If(DayName(CurrentDate())=”Sunday”;DayName(RelativeDate(CurrentDate();-2));DayName(RelativeDate(CurrentDate();-1))))

So using this formula we should end up with the following result:

Current Day Previous Working Day

Monday         Friday
Tuesday        Monday
Wednesday  Tuesday
Thursday     Wednesday
Friday           Thursday
Saturday      Friday
Sunday         Friday

%d bloggers like this: