Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
That works!
Finally
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
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
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);
Much appreciated. This really helped me understand
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