use of RIGHT, LEFT functions in a webI variable

Posted on 2012-09-21
Last Modified: 2012-09-24
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?
Question by:wdelaney05
    LVL 15

    Expert Comment

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

    Author Comment

    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?
    LVL 100

    Accepted Solution

    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

    LVL 34

    Expert Comment

    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.


    Author Comment

    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
    LVL 34

    Expert Comment

    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.


    Author Comment

    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
    LVL 34

    Expert Comment

    You're welcome.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
    Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
    The viewer will learn how to set up a document for the web and print and the recommended PPI for printing.
    The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now