Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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.

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