Populate drop down input box with DB field values?

[#1]

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?

[Digvijay_Singh]

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?

[#3]

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.

[petter]

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 ;

[#5]

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 )

[petter · Accepted solution]

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?

[#7]

That worked! Thank you for the help!

[#8]

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) ))

[petter]

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.

[#10]

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