topic Re: Predefined number series (dropdown) with dynamic max value in QlikView

Predefined number series (dropdown) with dynamic max value

Thu, 06 Mar 2014 18:08:37 GMT

I have an input box which provided the user with a drop down with a number series, currently hard-coded 1-12 in the variable settings. I want to be able to change the max value based on another variable/field.

- I am unable to enter an expression in the "To" field.

- I am also unable to set huge max ie: 1-1000 and then filter which values are visible based on another variable.

Any suggestions?

Re: Predefined number series (dropdown) with dynamic max value

maxgro — Thu, 06 Mar 2014 18:15:28 GMT

I tkink you can only use expressions in Custom and Error Message because there are the ... (dots)

from online help

Custom

Input values will be checked against a user specified constraint. The constraint is entered in the edit box and must be expressed as a QlikView expression returning TRUE (non-zero value) if the input value is acceptable. The input value is referenced as a dollar sign ($) in the expression.

Example:

$>0 will cause the input box to accept only positive numbers in the selected variable.

The previous value of the variable can be referenced by the variable name.

Example:

$>=abc+1 as a constraint on a variable named abc will cause the input box to accept only numeric entries with a value of the old value plus 1.

Re: Predefined number series (dropdown) with dynamic max value

swuehl — Thu, 06 Mar 2014 18:16:33 GMT

Instead of the Number Series, check 'Listed Values' and create your own series using an expression:

=concat(ValueLoop(1,100,1),';',ValueLoop(1,100,1))

You can use a variable as upper limit (i.e. replace 100 wiht e.g. $(vUpperLimit).

Re: Predefined number series (dropdown) with dynamic max value

Anonymous — Thu, 06 Mar 2014 18:34:11 GMT

this is interesting!

just curious but using the expression in swuehl's post, the input box displays one row - 1;2;3;4;5;6; and so on up to 100. it does not allow to input a single number within 1-100. also, there is no drop down of each number to 100 even if predefined values is selected in drop-down option.

am i missing something?

Re: Predefined number series (dropdown) with dynamic max value

Thu, 06 Mar 2014 18:34:49 GMT

This is VERY close, but for some reason it is displaying the range on one line instead of as distinct values to be selected individually. Two screen shots below:

First is when I have =concat(ValueLoop(1,100,1),';',ValueLoop(1,100,1))

Second is when I just list "1;2"

How can I make it so the concat doesn't result in single sting?

Re: Predefined number series (dropdown) with dynamic max value

Anonymous — Thu, 06 Mar 2014 18:49:29 GMT

exactly what i was referring to.

a very less cool way of doing it is may be the following. of course, more the number of options you have, less efficient/more cumbersome this will become!

if you only have a couple of options for max, then may be create two variables with their respective max values the way you created it to begin with and have two separate input boxes at the same coordinates on top of each other. use conditional show/hide based on selected option on the input boxes. in other charts, you will have to account for the selection and corresponding variable.

like i said, this may be your very last resort!

edit: or you could do this with inline tables and using fields instead of variables.

Re: Re: Predefined number series (dropdown) with dynamic max value

swuehl — Thu, 06 Mar 2014 18:51:29 GMT

Not sure why this don't work for you, seems to work for me (see attached sample with Variable1 as dropdown, range upper limit determined by Variable2).

Re: Predefined number series (dropdown) with dynamic max value

Thu, 06 Mar 2014 19:03:47 GMT

Its working as you mentioned in your file when i opened it, but even when i tried to copy the expression and put it in an input box i created it did not work.All the data was in one line.

This is interesting....

Re: Predefined number series (dropdown) with dynamic max value

Thu, 06 Mar 2014 19:05:33 GMT

Same results for me

Re: Predefined number series (dropdown) with dynamic max value

swuehl — Thu, 06 Mar 2014 19:07:51 GMT

Could you repost your sample application?

Re: Re: Predefined number series (dropdown) with dynamic max value

Thu, 06 Mar 2014 19:12:41 GMT

Here is a simple test app which should be identical to yours.

Re: Predefined number series (dropdown) with dynamic max value

rwunderlich — Thu, 06 Mar 2014 19:50:53 GMT

Jessica Ben You need to change your list separator to "," instead of ";". swuehl is in Europe so his list separator is ;.

You can find out what the ListSep is on your machine by looking at Help, Document Support Info from the QV menu.

To clarify why swuehl's example worked when downloaded but not copied. The Document ListSep is set from the Sys (machine value). So the example document provided set the Document ListSep to ; (semicolon). But when the expression was copied to a new North America document, it expected the Sys default of , (comma)

-Rob

http://masterssummit.com

Re: Predefined number series (dropdown) with dynamic max value

Thu, 06 Mar 2014 19:56:25 GMT

WONDERFUL!!! That does it!

Re: Predefined number series (dropdown) with dynamic max value

Thu, 06 Mar 2014 20:11:33 GMT

Hey Rob,

Quick question though.

If Listsep = "," for both Sys and Doc, then how come when you use 1;2;3 it works fine? I am little confused. Whats the difference between this and the one in the expression. why use ; in one place and , in another.

Also can you change Listsep?

Thanks

Re: Predefined number series (dropdown) with dynamic max value

rwunderlich — Fri, 07 Mar 2014 00:56:05 GMT

Ajay,

It's true that when you hard code the list, you use semicolon as the sep per the doc. When generating the list, you must use the sep that matches the Doc ListSep.

That difference has been a mystery to me for some time. My guess is a different parser is being used for the literal expression string vs the generated string. That's a fancy way of saying "I don't know".

This thread is where I first heard about the issue from Miguel Angel Baeyens

Input Box and Concat

I don't know of a way to neutralize the problem when coding the dynamic list. While you can get the Sys ListSep with the expression:

=getregistrystring('HKEY_CURRENT_USER\Control Panel\International','sList')

I don't know of any way to get the Doc ListSep. The good news is that once Doc ListSep is set, it doesn't change.

You can change ListSep via Window Control Panel, Regional Settings. But that may have some side effects.

-Rob

http://masterssummit.com

Re: Predefined number series (dropdown) with dynamic max value

Fri, 07 Mar 2014 01:27:57 GMT

Thanks for taking the time to explain Rob. Learnt something new today