topic Populate drop down input box with DB field values? in QlikView

Populate drop down input box with DB field values?

Mon, 28 Sep 2015 16:32:45 GMT

I want to set up a drop down input box on a report for a customer to select an available sales date from the database where I want the value of each available date in the database to appear in the drop down selector as one per row.

On the constraints tab I have the following setup:

Input Constraints = Predefined Values Only -- nothing else is selected or checked.

Value List = Predefined Values in Drop-down -- nothing else is selected or checked.

Predefined Values has the listed box checked with the following formula (-- nothing else is selected or checked):

=Concat( {1} DISTINCT SALES_DATE, chr(10) & chr(13) , SALES_DATE )

This doesn't work and wants to give a linear list of the SALES_DATE field. I want the list to place one date per line of the drop down input box.

Any help?

Re: Populate drop down input box with DB field values?

Digvijay_Singh — Mon, 28 Sep 2015 16:54:14 GMT

You can use list box of required date field to have desired drop down of date since your purpose is to select date from drop down, right?

Re: Populate drop down input box with DB field values?

Mon, 28 Sep 2015 16:59:52 GMT

I am looking to set a start-sale-date and end-sale-date variables that would be used to show only the data between the date range. I want the SALE_DATE from the DB to populate the drop down of the input box field.

Re: Populate drop down input box with DB field values?

petter — Mon, 28 Sep 2015 19:50:08 GMT

You should use the semicolon as separator - QlikView will put all the values on separate lines for you in the drop-down. So replace the Chr(10) & Chr(13) with ;

Re: Populate drop down input box with DB field values?

Mon, 28 Sep 2015 19:52:30 GMT

I tried that and it ran all of the dates together on one line with them being separated by a semicolon when using: =Concat( {1} DISTINCT SALES_DATE, ';' , SALES_DATE )

Re: Populate drop down input box with DB field values?

petter — Mon, 28 Sep 2015 19:54:28 GMT

There is an oddity with QlikView for some users that semicolon will not work for this drop-down. They seem to have to use comma instead. Could you please try that to see if it works for you?

Re: Populate drop down input box with DB field values?

Mon, 28 Sep 2015 19:56:07 GMT

That worked! Thank you for the help!

Re: Populate drop down input box with DB field values?

Mon, 28 Sep 2015 19:58:56 GMT

Do you know how to get it to stop sorting the data when I use it for a 'Month & Year' string in the same formula? =Concat( {1} DISTINCT (Month(THE_DATE) & ' ' & Year(THE_DATE) ), ',' , (Month(THE_DATE) & ' ' & Year(THE_DATE) ))

Re: Populate drop down input box with DB field values?

petter — Mon, 28 Sep 2015 20:03:54 GMT

The last parameter is how QlikView's Concatenate()-function should sort the data:

( Month(THE_DATE) & ' ' & Year(THE_DATE) )

It doesn't seem like that is how you want to sort it - does it? I would guess that THE_DATE alone or -THE_DATE (for descending dates) alone should be the sort order you want.

Re: Populate drop down input box with DB field values?

Tue, 29 Sep 2015 12:23:02 GMT

Thank-you! Using THE_DATE as the sort parameter worked!

Re: Populate drop down input box with DB field values?

Tue, 29 Sep 2015 16:31:18 GMT

Any reason you would know why the text box allows an initial selection of a date, but will not allow it to be changed afterwards?