4 Replies Latest reply: Nov 10, 2016 8:22 AM by Tanya Ruppell RSS

    Can I use single quote in concat?

    Tanya Ruppell



      I'm struggling with special characters today!


      I want to join my data into a single string with the following format:


      'valA' OR 'valB' OR 'valC'


      I've tried:

      • concat(field, chr(39) & ' OR ' & chr(39)) - this just gives me an invalid dimension
      • redefining my data to include the single quotes, so my values are 'valA', 'valB', 'valC' instead of valA, valB, valC, and when I look at them separately, the single quotes are there, but once I concat, they are gone and I get: valA OR valB OR valC


      Is there any way to do this?




        • Re: Can I use single quote in concat?
          Sunny Talwar

          May be this:


          Aggr(Concat(DISTINCT Chr(39) & field & Chr(39), ' OR '), <Aggregating dimension here>)

            • Re: Can I use single quote in concat?
              Tanya Ruppell

              This doesn't work either. Maybe it has to do with how I am trying to use the statement...


              First of all, I'm just trying to create one list with all of the selected set and put it in a dimension column of a table, so I don't need the Aggr function. But that's fine, I tried with just the concat part and it doesn't work. However, I've also confirmed that if I replace the Chr(39) with Chr(34) (double quote), it does work, so this is something specific to the single quotes.



              Perhaps you can tell me a better way to accomplish the goal of putting this string in a dimension column without so many steps and that might work. Here's how I'm doing it:


              I have a variable vTest defined as =Concat(DISTINCT Chr(39) & field & Chr(39), ' OR ') and then I have my dimension defined as ='$(vtest)'


              This was the only way I could find to get the function to execute and fill in the dimension column with the resulting string.



              I have actually accomplished my ultimate goal avoiding this string all together, so this is no longer needed for my task, but I would still like to know if there is a way to do it.



            • Re: Can I use single quote in concat?
              Marco Hadiyanto


              Have you tried textbetween functions?



              This function returns the text between the n:th occurrence of beforetext and the immediately following

              occurrence of aftertext within the string s.


              TextBetween(s , beforetext , aftertext [, n ])


              Examples and results:

              TextBetween('<abc>', '<', '>') Returns 'abc'

              TextBetween('<abc><de>', '<', '>',2) Returns 'de'