BusinessObjects Board

Extracting date from text field

building reports
#1

hi Guys,

Ive got the following formula which works for most of my data to extract a date from a text field;

=If(
Pos([Note]; “R”) > 0
And Length(Substr([Note]; Pos([Note]; “R”) + 1; 6)) = 6;
FormatDate(
ToDate(
Substr([Note]; Pos([Note]; “R”) + 1; 6);
“ddMMyy”
);
“yyyy/MM/dd”
);
“Invalid Date”
)

However the text field can contain other “R” values, and the formula does not account for this. Id like to update this formula to account for values beginning with “R” and followed by numeric values. Is there a way of doing this.

Below is some sample data that i’m extracting from, its the values 180723 I want to extract.
The ‘R180723’ is normally at the start of the text field but can be also in the middle. Fun!

“Please add to Reading list BMS999 crn 1234 Req
R180723 Oversize QP34.5.M27 2023 put previous ed on standard loan and withdraw 10th ed (2005) from stock”

Thanks
Gravy

#3

Is it always the only 6 digit number in the string?
Are you trying to create a date column from it?

I think I might look to regular expression usage (which won’t care if there is an R or not as long as it’s the only 6 digit field in the string)

[\d\d\d\d\d\d] should match it

I don’t have BO access just now, so I can’t be sure of the syntax, but it might be something like (his is mostly a complete guess cobbled together from a few search results)

=regex_replace( [Note], [\d\d\d\d\d\d], 
FormatDate( 
      ToDate( $1 );  “ddMMyy” 
    ); “yyyy/MM/dd”
  ); “Invalid Date” 
)

In other systems I’ve used $1 means the first matching regular expression. Meh, I’m not sure this is any help. But regex is where I’d look for a real solution.

Parsing free format user supplied text is one of the hardest tasks in computing.

Also the preformatted text option will let you show code that won’t get buggered up by the forum. Three back ticks (under the tilde) before and after the code…

=If( Pos( [Note]; “R” ) > 0 And 
      Length( Substr( [Note]; Pos( [Note]; “R” ) + 1; 6 ) ) = 6;

     FormatDate( 
      ToDate( Substr( [Note]; Pos( [Note]; “R” ) + 1; 6 ); 
      “ddMMyy” 
    ); “yyyy/MM/dd”
  ); “Invalid Date” 
)
#4

Thanks for the reply, I tried the regex_replace but Business Objects wasn’t liking it. I’m beginning to think this is maybe a step too far for webi formulas :slight_smile:

#5

Do you have any access at all to the underlying database?
Creating views perhaps?

Depending on your version of webi you might be able to use Free Hand SQL (so you have the actual DB SQL at your beck and call and not just webi parsing strings)

#6

Unfortunately I don’t have access to the database, its locked down. I’ll maybe play around with the custom sql like you suggest, but there is maybe only 15 records that this in impacting out of 6000, so its not a dealbreaker, i’m just always looking for perfection :slight_smile:

1 Like
#7

If you find a solution, go ahead and post it here - it seems like that would be of general interest.

I have the same problem with seeking perfection and user provided data is some of the dirtiest stuff around.