topic Help with making external selections in QlikView

Help with making external selections

terrykavouras — Thu, 27 Jul 2017 14:27:22 GMT

Users have a spreadsheet with a column of values. I'd like to make it easy for them to use those values as a selection in my QlikView app.

So far the only way I've found to do this is, in Excel, to concatenate the column of values and insert a vertical bar "|" as a separator into a single cell. The user then copies that cell and pastes it into an input field in the QV app. This is a klunky and inelegant solution. Can anyone think of a way to simplify this or come up with a better scheme to use a column of Excel values as selections in a QlikView app?

Re: Help with making external selections

effinty2112 — Thu, 27 Jul 2017 15:06:54 GMT

Hi Terry,

If you can access the script try this.

Before loading the table containing the field you are making selections on enter this. User Selections.xlsx is a spreadsheet with one column. The first cell is the fieldname the rest are the values.

UserSelections:

LOAD *,'Y' as XLselection

FROM

[User Selections.xlsx]

(ooxml, embedded labels, table is Sheet1);

Let vFieldName = FieldName(1,'UserSelections'); //Gets the name of the field the selections are made on

MappingUserSelections: Mapping LOAD * Resident UserSelections; //makes a mapping table from the above

DROP Table UserSelections;

At the point in your script where you load the table with the field you are selecting on add this as the top of a preceding load:

TableName:

Load

ApplyMap('MappingUserSelections',[$(vFieldName)],'N') as [User Selected];

Load

After the script has ran a listbox on the field [User Selected] should give the functionality you need.

If you need to do this through the front end then the method with the pipe '|' is what you're stuck with as far as I know.

Good luck

Andrew

Re: Help with making external selections

terrykavouras — Thu, 27 Jul 2017 15:19:00 GMT

Thanks for the thoughtful and clear answer. However I do need to allow the users to make the selections through the front end. Many different users use the app to search for different values from different spreadsheets, so I can't build it into the data model as you demonstrated.

Re: Help with making external selections

effinty2112 — Thu, 27 Jul 2017 20:48:23 GMT

Hi Terry,

Try this:

Add an inputbox to your front end and add a variable vList to it.

Copy a column out of your Excel sheet where the first cell is the name of the field and the rest are the values.

Paste that into the inputbox.

IMPORTANT: When you post into the inputbox you must do this. Click on the input box then click the grey button with the ellipsis (...) so you get the edit expression screen and paste in there. If you do that the data will be pasted in as a column. If you paste straight into the inputbox the data will not be accepted as a column.

Add these variables (not in the inputbox, use the Variable Overview)

vTrimList =Trim(Left(Replace('$(vList)',Chr(10),'|'), len(Replace('$(vList)',Chr(10),'|'))))

vField =SubField(vTrimList,'|',1)

vValues =Replace('(' & Right(vTrimList,len(vTrimList)-len(vField)-1) & ')',' ','?')

Nearly there.

Add a button, label it Apply Filter or something. Add the action Select in Field.

for Field enter : =vField

for Search String enter : =vValues

Now try it out.

Note: If any of the selected values contain a space eg. 'Ken Dodd' the search string passed to QV will look for 'Ken?Dodd'. If you search a listbox for a value containing a space you'll see why this was done. Hopefully this will work for you.

Good luck

Andrew

Re: Help with making external selections

terrykavouras — Fri, 22 Sep 2017 13:52:00 GMT

Andrew, thank you very much. It is a clever solution. This worked great, albeit with some small alterations. My final variable looks like this:

vTrimList: Trim(Left(Replace('$(vList)',Chr(10),'|'), Len(Replace('$(vList)',Chr(10),'|'))-1))

Note the need to use dollar-sign expansion on the vList variable. Also, I had to add parentheses and single quotes around the final variable like this:

vValues: chr(39) & '(' & $(vTrimList) & ')' & chr(39)

In the button I used the Select in Field command and simply entered the name of the field and set the Search String to: =$(vValues)