topic Re: Concatenate field value with single quotes in QlikView

Concatenate field value with single quotes

woshua5550 — Wed, 25 Nov 2020 16:16:04 GMT

Hi there

let's say I have a table like this

need a expression in text box to show this result

'A','B','C'

how to do this ? thanks

Re: Concatenate field value with single quotes

Or — Thu, 02 Nov 2017 11:48:06 GMT

Fun with chr(39)

=chr(39) & concat(F1,chr(39)&','&chr(39)) & chr(39)

Re: Concatenate field value with single quotes

sunny_talwar — Thu, 02 Nov 2017 11:49:52 GMT

Or this

Concat(DISTINCT Chr(39) & F1 & Chr(39), ', ')

Re: Concatenate field value with single quotes

woshua5550 — Thu, 02 Nov 2017 11:53:46 GMT

Nice ! thanks a lot

but there is no Escape Character in qlik expression syntax ?

Re: Concatenate field value with single quotes

sunny_talwar — Thu, 02 Nov 2017 11:55:33 GMT

There is, check this

Escape sequences

Re: Concatenate field value with single quotes

woshua5550 — Thu, 02 Nov 2017 11:58:00 GMT

Neat one !

Re: Concatenate field value with single quotes

woshua5550 — Thu, 02 Nov 2017 11:59:49 GMT

Love it

Re: Concatenate field value with single quotes

sunny_talwar — Thu, 02 Nov 2017 12:01:16 GMT

I think this should work

Concat(DISTINCT '''' & F1 & '''', ', ')

Here I have used four single quotes

Re: Concatenate field value with single quotes

woshua5550 — Fri, 03 Nov 2017 02:52:36 GMT

Hi Sunny

Actually it not , I find that we have to put a space after first single quote so that expression syntax will be ok

like this

Concat( ' ''' & F1 & ' ''', ',')

but we will have spaces in result weird

Re: Concatenate field value with single quotes

sunny_talwar — Fri, 03 Nov 2017 08:20:11 GMT

But why do you want to put spaces after first single quote? Why not this?

Concat(DISTINCT '''' & F1 & '''', ', ')

Re: Concatenate field value with single quotes

woshua5550 — Fri, 03 Nov 2017 09:19:26 GMT

it says expression error in my side , have you tried ?

Re: Concatenate field value with single quotes

sunny_talwar — Fri, 03 Nov 2017 11:26:40 GMT

Checking

Re: Concatenate field value with single quotes

sunny_talwar — Fri, 03 Nov 2017 11:31:55 GMT

Yes, you are right, for some reason it doesn't work.... I guess you can remove the space using PurgeChar or replace, but why would you want to do that when you have easier solutions, right?

=PurgeChar(Concat(DISTINCT ' '' ' & F1 & ' '' ', ', '), ' ')

Re: Concatenate field value with single quotes

woshua5550 — Sat, 04 Nov 2017 09:21:05 GMT

Haha,just curious

Re: Concatenate field value with single quotes

pietapa — Wed, 26 Apr 2023 08:50:24 GMT

Hello everyone,

I'm facing a similar issue to what was previously discussed, and I've tried the solution suggested, but it hasn't worked for me. My goal is to save a column of data into an array using the Peek function with the structure above, but I'm having trouble with it. Can someone please help me out?

Re: Concatenate field value with single quotes

BrunPierre — Wed, 26 Apr 2023 09:28:27 GMT

Post sample data and desired results.

Re: Concatenate field value with single quotes

pietapa — Wed, 26 Apr 2023 11:04:17 GMT

This Sample should be okay, I guess:
I have a table like below.

Name of Customer

Customer1
Customer2
Customer3
......

I would like to create a variable called 'vCustomers' that will contain the following values: 'Customer1', 'Customer2', 'Customer3', and so on. The reason for creating this variable is to use it in a SQL query.

SELECT * FROM (Table in Data Lake) WHERE Name IN (vCustomers);

Re: Concatenate field value with single quotes

Prem0212 — Wed, 26 Apr 2023 11:14:14 GMT

This expression Concat(DISTINCT Chr(39) & NAME & Chr(39), ', ') will work for you.

please accept the solution if u liked it.

Re: Concatenate field value with single quotes

pietapa — Thu, 27 Apr 2023 09:30:08 GMT

I tried your solution, but I received an empty variable. I am unsure why it did not work.