Putting Single Quotes around each value separated inside of a parentheses

[#1]

I am loading variable values from a spreadsheet by looping through an excel file. The value column may have one or more values. I need each value to have a single quote around it regardless of how many values are present in the cell.

LOAD
Variable,
chr(39)&Value&chr(39) as Value

I am getting this

Variable Name       Value

v_variable1             '12345'

v_variable2             '54321'

v_variable3            '(13579,97531)'

v_variable4            '(abcde,vwxyz)'

What I want:

Variable Name       Value

v_variable1             '12345'

v_variable2             '54321'

v_variable3            ('13579','97531')

v_variable4            ('abcde','vwxyz')

Any ideas?

[sunny_talwar]

May be something along these lines:

LOAD Variable,

          '(' & Concat(Chr(39) & Value & Chr(39), ',') & ')' as Value

Group By Variable;

LOAD Variable,

          PurgeChar(SubField(Value, ','), ')(') as Value

FROM Source;

UPDATE: Attaching the sample

[sinanozdemir · Accepted solution]

Edited: I had a typo in the script.

This could be another approach:

And here is your data model:

Hope this helps.

Other useful links by Sinan:

Handling Metadata in QlikView

QlikView and Python Integration

QlikView Automation Series – Unzip & Load Excel Files

[sunny_talwar]

I think you missed to add a single quote before the second string for variable3 and variable4

[sinanozdemir]

Thanks sunindia‌. I realized after posting it. I edited it.

[#6]

Thanks Sunny!

That is close to what I want but I am getting the below result. I think I can figure out how to remove the parentheses for single values.

[sunny_talwar]

Try this:

Table:

LOAD Variable,

          If(Count(Value) > 1, '(') & Concat(Chr(39) & Value & Chr(39), ',') & If(Count(Value) > 1, '(') as Value

Group By Variable;

LOAD Variable,

     PurgeChar(SubField(Value, ','), ')(') as Value

FROM

Community_203446.xlsx

(ooxml, embedded labels, table is Sheet1);

[#8]

Thanks Sinan! That is an interesting way to do it as I have rarely touched wildmatch.

[sunny_talwar]

Just a thought, does this still work if you have more than 2 strings? or would we have to make modifications to the script?

[#10]

That is close but the closing parentheses is inverted