Okay, I have a Webi report and instead of selecting some 100s of values for my in-list I tried doing a Copy+Paste.
So using Excel I formated a column to add a ‘;’ after each cell value.
I copied some 500 rows(or cells) and paste them in the Prompt list of my query.
Unfortunately by doing so Webi added a space(’ ‘) after each semi-colon which resulted in a space(’ ') starting each row of data. This resulted in NO Data Retrieved.
How can I remove this SPACE that gets added or can it be done!
I know there is data because I edited some of the spaces from the BO search box and they came up fine.
I don’t think adding 5 values (for e.g.) with ; tells Web-I query that you entered 5 different values. It still assumes that you just entered a string.
Actually it did work as it placed each value on a different line. But as mentioned, it added a space; so each line started out with a space(except for the very first value.
We used this method back at my previous gig, only time I recall issues was countries that used different seperator characters. We may have added a trim to the values - maybe Excel is formatting your data?
if you manually type in values seperated by ; do you get data?
Yes to both questions,
It works the same if I select the data from the selection box as it would if I were to go in the search box and remove the blanks
(i.e…
12345; 23456; 34567; 456789
This results in the values showing up as:
12345
sp 23456
sp 34567
sp 456789
if I edit these same 5 to
12345;23456;34567;456789
The results in the values showing up as:
12345
23456
34567
45678
This works
Now, I don’t know if its Excel but think its Webi as it takes every ‘;’ and adds a space after it(like an extra CR)
If your data looks like that, maybe I do have something to add…
We found that BO wanted commas for numbers, semicolons for text - the rules may be vague as to what it considers a number, but do you get better luck with commas?
I think we had TWO columns in our spreadsheet macro, one for numbers and one for characters - plug your data in that column, hit a button, and cut-paste from a results cell…
If you do that, do you have any code to strip spaces out as part of the concatenation? Just curious, not sure I got your answer to that bit.
Wait, isn’t that what you’re doing? I must have misread your post.
We took a column of cells, a1…a9999, put them togther into 1 cell to cut and past. trim(a1)+";"+trim(a2)+";’… Tie that to a button / run a macro, execute vb code to do that concatenation.
That works, certainly into XI 3x versions. looking for examples now, but the macro was only about 8 lines long…
Here’s a simple macro that should do what I describe… Plug in your delimiter to cell C1, all your values in column A (1-24 in my example below, leave blank what you don’t want).
B
Sub ConcatCells()
Dim ConcatChar As String
Dim ResultCell As String
Dim CellConts As String
ConcatChar = Range("c1").Value
ResultCell = ""
'
' cells a1-a24 contain values to concatenate (with C1 delimiter)
'
For Each CellContsCell In Range("a1:a24").Cells
If CellContsCell.Value <> "" Then
If ResultCell <> "" Then
ResultCell = ResultCell + ConcatChar + Trim(CellContsCell.Value)
Else
ResultCell = Trim(CellContsCell.Value)
End If
'MsgBox (ResultCell)
End If
Next CellContsCell
' display results in E2
Range("e2").Value = ResultCell
End Sub
(BOB member since 2008-05-12)
(BOB member since 2002-08-15)
(BOB member since 2002-08-29)