• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4616
  • Last Modified:

use of RIGHT, LEFT functions in a webI variable

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


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?
1 Solution
Ess KayEntrapenuerCommented:
use a search for a comma in the field.
if there is a comma take the LEFT side until the comma
wdelaney05Author Commented:
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?
Can you accept

Lab Test A           12345
Lab Test B           12345
Lab Test C            23789

If so you can create a formula to replace the , with chr(13)
You will have to set the field for auto height

If you need it to look like your example then I believe you will have to create objects in the unvierse so you return one record for each CPT_Code_List_break_out list value

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

wdelaney05Author Commented:
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
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.

wdelaney05Author Commented:
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!

W Delaney
You're welcome.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now