Can I use single quote in concat?

[Anonymous]

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

[sunny_talwar]

May be this:

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

[Anonymous]

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!

[marcohadiyanto]

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

[Anonymous]

Hi Marco,

I think maybe you misunderstood my question. It was specifically about wanting to put single quotes into the string generated by concat. The problem seems to be that there is no way to get Qlik Sense to treat the single quote as a literal character, it always interprets it instead.

Thanks,

Tanya

[mtucholski]

Hello, 

I know that this is an old topic but I faced the same problem and found the solution.

replace(concat(field_name,','),',',chr(39)&','&chr(39))

concat return the values seperated by " , " e.g. a,b,c,d

then replace replaces " ,  "to " ',' " so a,,b,c,d turns into a','b','c','d

cheers