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]

Hahahaha copy pasted the first if statement at the end

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

Hopefully there are no more errors

[#3]

That works!

[sunny_talwar]

Finally

[#5]

Sunny, I'm going to bother you one more time. I really want to understand this

How would one have single quotes around the values inside of the parentheses but no quotes around the values with no parentheses

[sunny_talwar]

Lets look at an example to help you understand that. NOTE: I have taken the liberty to add a variable with 3 strings. So the data looks like this now:

1st step:

Script

Table:

LOAD Variable,

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

FROM

Community_203446.xlsx

(ooxml, embedded labels, table is Sheet1);

Seperates out each of the Value into a different row (using SubField() function)

Next I add the Count(Value) by each variable:

Script

Table:

LOAD Variable,

  Count(Value) as Count

Group By Variable;

LOAD Variable,

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

FROM

Community_203446.xlsx

(ooxml, embedded labels, table is Sheet1);

I see that variable1 and variable2 has only one value. So I don't need parenthesis there, but the other places I do need them.

So my final script:

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

Here in the highlight part you can see that I am checking for if the count is > 1. If it is add parenthesis, else leave it null().

Does all of the above help? I usually break a problem into parts to solve it and it becomes so much easier. Try this approach in the future and I am sure you will find yourself resolving 9 out of the 10 problems

Best,

Sunny

[sunny_talwar]

Misread the questions

Check this script:

Table:

LOAD Variable,

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

// Count(Value) as Count

Group By Variable;

LOAD Variable,

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

FROM

Community_203446.xlsx

(ooxml, embedded labels, table is Sheet1);

[#8]

Much appreciated. This really helped me understand

[sunny_talwar]

If you find any of the things above to be helpful, please select them as helpful. Why? Look here -> Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny