Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Is there any way to do this?
Thanks,
Tanya
May be this:
Aggr(Concat(DISTINCT Chr(39) & field & Chr(39), ' OR '), <Aggregating dimension here>)
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!
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
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
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