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

    Can I use single quote in concat?

    Tanya Ruppell

      Hello,

       

      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?

       

      Thanks,

      Tanya

        • 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.

               

              Thanks!

            • Re: Can I use single quote in concat?
              MARCO HADIYANTO

              Hi,

              Have you tried textbetween functions?

               

              TextBetween

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

              occurrence of aftertext within the string s.

              Syntax:

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

               

              Examples and results:

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

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

               

              Regards,

              Marco