Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Putting Single Quotes around each value separated inside of a parentheses

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?

1 Solution

Accepted Solutions
sinanozdemir
Valued Contributor III

Re: Putting Single Quotes around each value separated inside of a parentheses

Edited: I had a typo in the script.

This could be another approach:

Capture.PNG

And here is your data model:

Capture.PNG

Hope this helps.

Other useful links by Sinan:

Handling Metadata in QlikView

QlikView and Python Integration

QlikView Automation Series – Unzip & Load Excel Files

17 Replies

Re: Putting Single Quotes around each value separated inside of a parentheses

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
Valued Contributor III

Re: Putting Single Quotes around each value separated inside of a parentheses

Edited: I had a typo in the script.

This could be another approach:

Capture.PNG

And here is your data model:

Capture.PNG

Hope this helps.

Other useful links by Sinan:

Handling Metadata in QlikView

QlikView and Python Integration

QlikView Automation Series – Unzip & Load Excel Files

Re: Putting Single Quotes around each value separated inside of a parentheses

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

sinanozdemir
Valued Contributor III

Re: Putting Single Quotes around each value separated inside of a parentheses

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

Not applicable

Re: Putting Single Quotes around each value separated inside of a parentheses

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.

Highlighted

Re: Putting Single Quotes around each value separated inside of a parentheses

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);

Not applicable

Re: Putting Single Quotes around each value separated inside of a parentheses

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

Re: Putting Single Quotes around each value separated inside of a parentheses

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

Not applicable

Re: Putting Single Quotes around each value separated inside of a parentheses

That is close but the closing parentheses is inverted

Community Browser