topic Re: Multi select (light red|blue|green) doesn't work? in QlikView

Multi select (light red|blue|green) doesn't work?

Fri, 29 Nov 2013 12:58:44 GMT

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.

thank you

Grant

Re: Multi select (light red|blue|green) doesn't work?

rbecher — Fri, 29 Nov 2013 13:18:38 GMT

How about (light?red|blue|green) ?

- Ralf

Re: Multi select (light red|blue|green) doesn't work?

hic — Fri, 29 Nov 2013 13:46:36 GMT

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

("*red"|"blue"|"green")

The trick is to use double quotes on all three values - or on none (but then you can't have spaces).

HIC

Re: Multi select (light red|blue|green) doesn't work?

Fri, 29 Nov 2013 13:46:50 GMT

Ralf,

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.

thank you

Re: Multi select (light red|blue|green) doesn't work?

Fri, 29 Nov 2013 13:55:38 GMT

Henric,

Thank you for the reply. Now I know. I will try the solution I mentioned in my reply to Ralf.

Re: Multi select (light red|blue|green) doesn't work?

Fri, 29 Nov 2013 15:01:14 GMT

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.

Sub Search_String()
    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
    Selection.ClearFormats
            With .Range(IDcol & FirstDataRow).Resize(rws) 'resizes the range to include both columns
                .FormulaR1C1 = "=CONCATENATE(RC[1],""|"")" 'builds the search string in the cell to the left using the concatenation formula"
                .Value = .Value 'converts the formula to a value
            End With
            .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
                Next cell
                    With Selection
                        .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
                    End With
                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
                    End With
        .Range(IDcol & FirstDataRow).EntireColumn.Delete 'deletes the new column

    End With

    Application.ScreenUpdating = True ' turns screen updating back on

    MsgBox ("Search String Created")

err_handler:
Application.ScreenUpdating = True
End Sub