Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

17 Replies
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

Not applicable
Author

That works!

sunny_talwar

Finally

Not applicable
Author

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:

Capture.PNG

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)

Capture.PNG

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


Capture.PNG

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().

Capture.PNG

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

Capture.PNG

Not applicable
Author

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