Web Intelligence – Extracting a date from a string

This article discusses a solution to a problem encountered by one of our clients.

In this particular case, our client has a string field which is populated with some free form text; a comment. When this data is pulled into the data warehouse, the date the comment was entered is appended to the comment.

The client wanted to create a Web Intelligence report that would bring back comments made over the past N number of days.

A number of issues meant this was not a simple request.

First – The comment creation date is not held seperately within the data warehouse and we have no quick means of changing the data warehouse to do so.
Second – The date has been appended to the comment as one long string, so the date format has been lost.
Third – Finally, the user wanted some flexibility to the report to select the last N number of days.

The solution:

The solution to the problem required several steps.

Step 1
The first step was to create an object to return the current date less a number of days specified by the user. We created a formula which prompted the user for a value (the number of days), and we subtracted that value from the current date. As we wanted to keep the user input, we used a prompt:

convert(datetime,convert( varchar,dateadd(dd,-@Prompt(‘Go back N days’,’N’,,MONO,FREE),getdate()),102))

When the user selects this object in the query panel, we get two values. The first is the result of the prompt ‘Go back N days’. This will be used in the report later. The second is a date value which is the current date less the number of days specified by the user. This will also be used in the report.

Step 2
The second step was to isolate the date field from the comment. Luckily the date is held in a constant format (Mmm dd yyyy) and is always at the end of the comment. The date part of the string is always eleven characters long.

Using the ‘Right’ function, we created a variable as follows:

=Right([Retail_User_Comment];11)

This tells Web Intelligence to read eleven positions back from the end of the string ‘Retail_User_Comment’.

This was stored in a variable called v_Comment_Date. We will use this later.

Step 3
The next step was a bit more tricky. The date field is held in a character string format. We wanted this in a date format. To do this the component parts were broken down into day, month and year.

Day:
Left(Right([v_Comment_Date];7);2)

Month:
If(Left([v_Comment_Date];3)=”Jan”;1;
   If(Left([v_Comment_Date];3)=”Feb”;2;
      If(Left([v_Comment_Date];3)=”Mar”;3;
         If(Left([v_Comment_Date];3)=”Apr”;4;
            If(Left([v_Comment_Date];3)=”May”;5;
               If(Left([v_Comment_Date];3)=”Jun”;6;
                  If(Left([v_Comment_Date];3)=”Jul”;7;
                     If(Left([v_Comment_Date];3)=”Aug”;8;
                        If(Left([v_Comment_Date];3)=”Sep”;9;
                          If(Left([v_Comment_Date];3)=”Oct”;10;
                            If(Left([v_Comment_Date];3)=”Nov”;11;
                              If(Left([v_Comment_Date];3)=”Dec”;12
                              )
                            )
                          )
                        )
                      )
                    )
                  )
                )
             )
          )
        )

Year:
Right([v_Comment_Date];4)

The items were then concatenated together, seperated by a forward slash using the Concatenation function.

Finally, the whole string was converted into a date field in the format we required dd/MM/yyyy. The resulting formula is as follows:

=ToDate(Concatenation(Concatenation(Concatenation(Concatenation(FormatDate(ToDate(Left(Right([v_Comment_Date];7);2);”dd”);”dd”);”/”);FormatDate(ToDate(If(Left([v_Comment_Date];3)=”Jan”;1;If(Left([v_Comment_Date];3)=”Feb”;2;If(Left([v_Comment_Date];3)=”Mar”;3;If(Left([v_Comment_Date];3)=”Apr”;4;If(Left([v_Comment_Date];3)=”May”;5;If(Left([v_Comment_Date];3)=”Jun”;6;If(Left([v_Comment_Date];3)=”Jul”;7;If(Left([v_Comment_Date];3)=”Aug”;8;If(Left([v_Comment_Date];3)=”Sep”;9;If(Left([v_Comment_Date];3)=”Oct”;10;If(Left([v_Comment_Date];3)=”Nov”;11;If(Left([v_Comment_Date];3)=”Dec”;12))))))))))));”MM”);”MM”));”/”);FormatDate(ToDate(Right([v_Comment_Date];4);”yyyy”);”yyyy”));”dd/MM/yyyy”)

This variable was named v_Comment_Date2.

Step 4:

We now have the date field isolated from the character string. Now we want to see rows which have been updated in the last N days. If you remember, we prompted the user for a number of days. This is held in the ‘Go back N days’ prompt.

Using the ‘DaysBetween’ function, we can create the following formula:

=If(DaysBetween([v_Comment_Date2];[Go back N days])

If the days between the comment date and the ‘Go back N days’ date is less than the value entered by the user, we return a 1 otherwise we return a 0.

We save this formula as a measure called v_Comment_Filter. It is important that this is saved as a measure otherwise we would not be able to filter on this value.

Step 5:

The final step is to filter the v_Comment_Filter object to show records with a 1 value, i.e. those records with a comment date less than the numberof days entered by the user at the prompt.

When the user now refreshed the report, he or she is presented with a prompt for number of days to go back, and is presented with a report with comments updated N days ago.

I hope you found this useful.

%d bloggers like this: