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?
Edited: I had a typo in the script.
This could be another approach:
And here is your data model:
Hope this helps.
Other useful links by Sinan:
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
Edited: I had a typo in the script.
This could be another approach:
And here is your data model:
Hope this helps.
Other useful links by Sinan:
I think you missed to add a single quote before the second string for variable3 and variable4
Thanks sunindia. I realized after posting it. I edited it.
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.
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);
Thanks Sinan! That is an interesting way to do it as I have rarely touched wildmatch.
Just a thought, does this still work if you have more than 2 strings? or would we have to make modifications to the script?
That is close but the closing parentheses is inverted