Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

MVP
MVP

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

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

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

That works!

MVP
MVP

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

Finally

Not applicable

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

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

MVP
MVP

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

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


MVP
MVP

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

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

Highlighted
Not applicable

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

Much appreciated. This really helped me understand

MVP
MVP

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

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