topic Input box with Predefined values coming from a Field in QlikView

Input box with Predefined values coming from a Field

Tue, 14 Jun 2011 02:21:41 GMT

Hi,

I can't seem to get this to work...

I just need an input box to set a value for a variable, but want to constain the possible values to what is stored in a particular field (a drop-down list would be the best).

Any pointers?

TIA

bob

Input box with Predefined values coming from a Field

pat_agen — Tue, 14 Jun 2011 07:10:44 GMT

hi,

create the input box. Go to the Properties->Constraints tab of the input box. Highlight yourfield. In t ebottom rght hand corner of the screen click on "predefined Values in Drop-down" and "Listed Values". In the Expression box under Listed Values enter an expresson such as this:

=concat(distinct YourFieldHere),';')

this will create a semi colon delimited list o fthe values form the field you wish to use. This is te format required for te contsraints in an input box.

try it and see.

Input box with Predefined values coming from a Field

Tue, 14 Jun 2011 07:16:25 GMT

Hi,

I have tried this before...But the dropdown lists the values in a single row like Value1;Value2;Value3

Input box with Predefined values coming from a Field

Tue, 14 Jun 2011 08:01:33 GMT

Almost!....

The problem is the values are text, and the requirement for text as listed values is that they must be enclosed by single quotes.

I tried using the string operator '&' with the chr(39) text function to add a single quote at the start & end of the expression, but that's still not enough, as it really needs the quotes surrounding the semicolon as well....

Input box with Predefined values coming from a Field

Miguel_Angel_Baeyens — Tue, 14 Jun 2011 08:06:00 GMT

Hi,

The above works fine here. What values are you using and what expression? Have you tried creating a new inputbox and not modifying the existing one?

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Input box with Predefined values coming from a Field

Tue, 14 Jun 2011 08:13:27 GMT

Hi Miguel,

Can you post the sample application for this

Input box with Predefined values coming from a Field

pat_agen — Tue, 14 Jun 2011 08:16:45 GMT

hi,

could you post an example?

just retested here and on a text field I did exactly as specified and it worked. Tried with another textfield having strings with spaces in them and it worked too.

I definitely don't think the semi colons [;] need placing inside quotes

from the help I can see this:

Listed Values

Check this alternative to specify a list of arbitrary predefined values. The values can be either numeric or alphanumeric. Alphanumeric values must be enclosed in quotes (e.g. 'abc' ). Each value is separated by a semicolon ( ; ) (e.g. 'abc';45;14.3;'xyz' ). This option can be used alone or in combination with Number series.

and I imagine thsi is what you are referring too. But I have the feeling the concat() expression gets around this.

I am missing something?

Re: Input box with Predefined values coming from a Field

Tue, 14 Jun 2011 08:23:25 GMT

pat.agen and Miguel,

The field that I want the predefined values holds dates in the format of YYYY-MM-DD, as follows:

The expression I used is therefore:

=concat(distinct TransacDate,';')

The result that I get is:

Input box with Predefined values coming from a Field

pat_agen — Tue, 14 Jun 2011 08:28:30 GMT

hi,

then format it with Date()

=concat(distinct Date(Timestamp,'AAAA-MM-DD'),';')

Input box with Predefined values coming from a Field

Tue, 14 Jun 2011 08:40:39 GMT

No joy....

I have tried

=concat(distinct date(TransacDate,'YYYY-MM-DD'),';')

and also

=concat(distinct date#(TransacDate,'YYYY-MM-DD'),';')

... the results are the same as depicted above.

Re: Input box with Predefined values coming from a Field

Miguel_Angel_Baeyens — Tue, 14 Jun 2011 08:52:45 GMT

Hi,

Check the attached.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Input box with Predefined values coming from a Field

pat_agen — Tue, 14 Jun 2011 08:53:58 GMT

hi,

just tried the first version again

=concat(distinct date(Timestamp,'YYYY-MM-DD'),';')

and it still works fine over here (am on v8.5). Have you tried starting over and with creation of a new variable? Apart from that only help I can give you is if you load your qvw as there may be something in there preventing this working ok.

Input box with Predefined values coming from a Field

Wed, 18 Jan 2012 23:47:04 GMT

Hi , I have a similar requirement, however, my input field is in TableBox. Basically I have a TableBox with two columns. One of the columns in InputField. So a particular cell in that column can be edited. However, I want to restrict user to be able to enter a value only from one of the existing values for column. Is this even possible?

Input box with Predefined values coming from a Field

jslancas — Tue, 24 Jan 2012 20:56:39 GMT

I have done the same thing, although I'm not using dates, and it works great.

One note, though, which should help @rajayaad is that if you replace the semi-colon with a comma, it will force a carriage-return, which will allow you to see the data in a column and just click (using a drop-down box) to select one of the items (rows). Then you can use the variable in a selection filter, or other expression. This is what I am using in the Constraints/Listed Values expression:

='None Selected,' & Concat(DISTINCT Replace(primpract_res_name,',','_'),',')

I also added 'None Selected' to the top of the list and forced a carriage-return after it, by including a comma. Since the field that I am using actually has commas in the field values themselves, I had to replace the comma with an underscore ("_"), then swap them back at the time of use. Otherwise, first names are listed on one line and last names on the next, which doesn't work. If you don't have commas to deal with, just use something like this:

='None Selected,' & Concat(DISTINCT fieldname,',')

'Hope this is helpful.

Input box with Predefined values coming from a Field

Mon, 26 Mar 2012 14:14:16 GMT

Thank you all, this is very helpful. I used what Jeff posted and here is my current Expression for which values to show as 'listed values'

='None Selected,' & Concat(DISTINCT {<File_Date=>} Replace(Date(File_Date,'M/D/YYYY'),',','_'),',')

I want to now sort the dates in descending order so when the user selects the dropdown they see the most recent dates first. How do I add a 'sort' into this expression?

Thank you in advance!

-Michael

Re: Input box with Predefined values coming from a Field

Tue, 24 Jul 2012 19:47:38 GMT

Hello:

The following should work:

=Concat(DISTINCT Date(File_Date,'M/D/YYYY'),',',Num(File_Date)*-1)

Num(File_Date)*-1 sorts by descending. Just use File_Date if you want ascending.

Regards,

Ben D.

Re: Input box with Predefined values coming from a Field

Wed, 23 Apr 2014 07:49:36 GMT

Hi Shanti,

You'll just need to replace the semicolon with comma:

=concat(distinct YourFieldHere),',')

Re: Input box with Predefined values coming from a Field

madhumitha — Mon, 01 Jun 2015 05:22:30 GMT

This worked for me Shanti,

Thanks for the help.

Re: Input box with Predefined values coming from a Field

anguila — Sat, 30 Jan 2016 11:31:27 GMT

Just to highlight the issue.. If you use a predefined/static string in the list values, like: 1;2;3;4, you need to separate them using semi colon, ;.

But if you're using an expression, you have to separate them with coma.

That's why, in previous comments, some people were saying they had date1;date2;date3 in one single row and not in a drop down list.

Re: Input box with Predefined values coming from a Field

Wed, 28 Sep 2016 13:34:47 GMT

Thank you David 🙂