asked on

use of RIGHT, LEFT functions in a webI variable

Hi there,
I have a field named CPT_Code_List that contains values such as

12345
12345,5678
23789

sample data for lab tests and their CPT codes will look like this:
Lab_Test_Name CPT_Code_List
Lab Test A 12345
Lab Test B 12345, 5678
Lab Test C 23789

The CPT_Code_List contains codes for certain lab tests. Some lab tests can have multiple CPT codes, hence the multiple values in the field sometimes. Is it possible, with the use of a RIGHT or LEFT function to get single values broken out from the CPT_Code_List
field? So that I can get data results that look like this in a Webi report:
Lab_Test_Name CPT_Code_List_break_out
Lab Test A 12345
Lab Test B 12345
Lab Test B 5678
Lab Test C 23789
Is it possible to use the RIGHT or LEFT functions in a webI variable to do this?

Ess Kay

use a search for a comma in the field.
if there is a comma take the LEFT side until the comma

W D

ASKER

Re: if there is a comma take the LEFT side until the comma.
Does that mean that I'll only get the value 12345 for Lab Test B?

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James0628

FWIW, if mlmcc's suggestion for splitting the codes onto separate lines is acceptable, then you could use another formula to reproduce the name for each code. Simplest case, assuming that every "," will be preceded and followed by a code (eg. you won't have something like ",123" or "12345, 678,"):

ReplicateString ({Lab_Test_Name} + ChrW (10),
Length ({CPT_Code_List}) - Length (Replace ({CPT_Code_List}, ",", "")) + 1)

All that does is repeat the name, followed by a line feed, once for every comma, plus 1. If there's no ",", you get the name once. If there is 1 ",", you get the name twice. And so on.

To count the commas, it replaces them with nothing ("") and subtracts the length of that string from the length of the original.

James

W D

ASKER

James0628, I tried mlmcc's suggestion for splitting the codes onto separate lines but WebI will still put them in the same "cell" displayed exactly how his example looks for Lab Test B.

Unfortunately, this isn't the desired result I was hoping for. The ReplicateString is Business Objects function for Crystal Reports but not WebI (darn). I'm going with mlmcc's suggestion for creating the object in the universe so that one record is returned for each CPT_Code_List_break_out list value. the client can choose that object, then, should they want to do counts of the lab tests by CPT code using the CPT code as a grouping.

also, I need to offer apologies. I chose Business Objects as the Zone, but it looks like I didn't unselect the radio button for Crystal Reports. It's not a Crystal Reports inquiry, rather, it's a Web Intelligence (WebI) inquiry. I don't think EE offers a WebI section, at least not yet.

I am most grateful that anyone gave my question a try. Thanks to all - I appreciate your promptness and professionalism to the nth degree!

Best regards,
w delaney

James0628

Yeah, if you use a formula to split the codes, they'll be on separate lines, but they'll still be in one field on the report. This would be true in CR too. If you're just viewing or printing the report, that doesn't seem like a problem. But if you were exporting the report, it might be.

I haven't used WebI, so I was just hoping that you were using CR for the report, or had a function like ReplicateString available. The same kind of thing could be done in another way, like using a loop, but I don't know what options you have in WebI.

James

W D

ASKER

thanks, James!
Not all is lost, though. I've never heard of ReplicateString and since I also develop Crystal Reports, I'm going to put this one in my bank of knowledge. It seems very useful!

Regards,
W Delaney

James0628

You're welcome.

James