I have been able to set a multi-selection on a field using the following syntax: (red|blue|green)
However, when I try something like (light red|blue|green) it fails. It does not seem to like the space between "light" and "red". I have tried using quotation marks e.g. ("light red"|blue|green) , ('light red'|blue|green) but I can't seem to come up with the right combination. We have QlikView 9 on the Server.
I'm sure I'm missing the obvious. If someone could point me in the correct direction, it would be appreciated.
There are unfortunately some inconsistencies in the composite search. We are addressing those, but they have not been fixed yet.
Ralf's suggestion should work. Or, you add quotes around all three strings and use wild cards, e.g.
("light red"|"blue"|"green") or
The trick is to use double quotes on all three values - or on none (but then you can't have spaces).
Thanx for the response. I should be able to use it.
As odd as it is, this is what I'm doing.
I am creating the "search" string in MS Excel with vba by concatenating cell values together with | and copying it to the clipboard. The user then pastes it into a list box's search area.
I will change the vba to replace any "spaces" with "?'s" in the search string. This should work but I was hoping that I could avoid doing so.
Thanx everyone. If you are interested in what I was trying to do. Here is a little more information. We often get Excel workbooks from users with lists that they want to "search" for in QlikView. This is a work around that they can use without loading a table. e.g.
Here is the code that I put together for Excel. The user selects the first cell and the macro creates the search string and copies it to the clipboard. I pasted the resulting string in the cell but the user would paste in to a QlikView Search area.
Not very elegant but seems to work.
Dim rws As Long
Dim IDcol As String
Dim FirstDataRow As Long
Dim FirstPart As String
Dim LastPart As String
Dim outputText As String
Dim strFICSearch As String
Const Delim = ""
On Error GoTo err_handler
Application.ScreenUpdating = False 'stops the screen from showing the execution of the code
IDcol = Split(ActiveCell(1).Address(1, 0), "$")(0) 'Gets the column letter(s)
FirstDataRow = ActiveCell.Row 'Gets the 1st row number to be sorted
With ActiveSheet 'determines the sheet where the rows to sort are located
rws = .Range(IDcol & Rows.Count).End(xlUp).Row - FirstDataRow + 1 'determines the rows to sort - stops at the first blank row
.Columns(IDcol).Insert 'inserts a new column to the left of the sort columnActiveCell.Columns("A:A").EntireColumn.Select
With .Range(IDcol & FirstDataRow).Resize(rws) 'resizes the range to include both columns
.FormulaR1C1 = "=CONCATENATE(RC,""|"")" 'builds the search string in the cell to the left using the concatenation formula"
.Value = .Value 'converts the formula to a value
.Range(IDcol & FirstDataRow, ActiveSheet.Range(IDcol & FirstDataRow).End(xlDown)).Select 'determines and selects the contiguous range of VINs
For Each cell In Selection
outputText = outputText & cell.Value & Delim 'basically concatenates each cell's value in a string
.Clear 'clears the cells contents in prepararion for the merging of the cells
.Cells(1).Value = outputText 'sets the first cells contents to the search string
'.Merge 'merges the cells in the range
Selection.Cells(1).Value = Replace("(" & left(outputText, Len(outputText) - 1) & ")", " ", "?") 'deletes the last | in the string,adds parentheses and replaces spaces with ?'s as a wildcard
With New DataObject 'creates a new data object
.SetText Range(IDcol & FirstDataRow).Text 'sets its text value to the cell's content i.e.the search string
.PutInClipboard 'put the text onto the Windows clipboard for use later
.Range(IDcol & FirstDataRow).EntireColumn.Delete 'deletes the new column
Application.ScreenUpdating = True ' turns screen updating back on
MsgBox ("Search String Created")
Application.ScreenUpdating = True