topic Re: Lixt Box with Ranges of Values in QlikView

Lixt Box with Ranges of Values

Fri, 19 Feb 2016 16:46:43 GMT

Given a data table of people that includes, for each person record, a numeric value called NumberOfMonthsSinceLastIncident, I want a list box with these filter values:

Show People with NO Incidents in Last 3 Months

Show People with NO Incidents in Last 6 Months, etc.

Show ONLY People with Incidents in Last 3 Months

Show ONLY People with Incidents in Last 6 Months, etc.

Show ALL People

I have this working with buttons and actions. I have an action behind each button on the field NumberOfMonthsSinceLastIncident with Search Strings >3, <=3, >6, <=6, etc. "Show ALL People" has a search string of ''. But we want to move this to a list box.

999 is the default for people with no incidents.

How might I accomplish this? Thank you.

Re: Lixt Box with Ranges of Values

sunny_talwar — Fri, 19 Feb 2016 16:49:36 GMT

There are few ways to do this. One is to create an inline table with the options you have and then drive your expression using if or pick match. There are other ways to do this as well. May be it would be easier to help if you can share a sample with us.

Re: Lixt Box with Ranges of Values

swuehl — Fri, 19 Feb 2016 16:53:26 GMT

Since a Person can belong to multiple classes, you need to create multiple records per person something like

LOAD Person,

'ALL' AS CLASS

RESIDENT YourPersonTable;

LOAD Person,

'Show ONLY People with Incidents in Last 3 Months' AS CLASS

RESIDENT YourPersonTable

WHERE NumberOfMonthsSinceLastIncident <=3;

LOAD Person,

'Show ONLY People with Incidents in Last 6 Months' AS CLASS

RESIDENT YourPersonTable

WHERE NumberOfMonthsSinceLastIncident <=6;

etc.

This table links to YourPersonTable via Person, and creates a new CLASS field for your selections.

Re: Lixt Box with Ranges of Values

Fri, 19 Feb 2016 17:06:55 GMT

Thank you, but isn't that a lot of overhead? And like I said, I already have this working with buttons. Imagine a listbox which is based on NumberOfMonthsSinceLastIncident. It has integer values from 0 to around 48, then 999. Clicking the Show ONLY People with Incidents in Last 3 Months button, for example, selects 0, 1, and 2 in the listbox, and the main data table adjusts accordingly. This is accomplished using an action on NumberOfMonthsSinceLastIncident and the search string <3. I simply want to move these actions to a listbox as described in my first post (listbox would necessarily have "Always One Selected Value" checked).

Re: Lixt Box with Ranges of Values

swuehl — Fri, 19 Feb 2016 18:46:51 GMT

Maybe I misunderstood you request.

What I suggested would create a new field for your classification, with all the benefits that adding a field to your data model shows: associative experience, able to select / filter values in list boxes as well as using this new field as dimension in charts etc. But probably not what you have been asked for.

If you just want to replace your buttons with a list box, a data island with just the values you want to show

LOAD *, RecNo() as ClassID INLINE [

Class

Show People with NO Incidents in Last 3 Months

Show People with NO Incidents in Last 6 Months

Show ONLY People with Incidents in Last 3 Months

Show ONLY People with Incidents in Last 6 Months

Show ALL People

];

should do.

Create an OnSelect Field event trigger in Settings - Document properties - Triggers on field Class.

Then use a Selection - Select in field action, field name

NumberOfMonthsSinceLastIncident

and as search string:

=pick( ClassID, '>3','>6','<=3', '<=6','*')

This would be just a replacement for your current approach.

Re: Lixt Box with Ranges of Values

sunny_talwar — Fri, 19 Feb 2016 18:52:13 GMT

That is exactly what I proposed as well, without an example

Re: Lixt Box with Ranges of Values

rwunderlich — Fri, 19 Feb 2016 19:08:29 GMT

"Thank you, but isn't that a lot of overhead?"

Stefan's data island method works, but I would suggest that you will have less overhead by linking something into the model as opposed to a data island. You have to open script anyways to create the island table, Why not create a table linked into the model? I would link to the NumberOfMonthsSinceLastIncident field rather than the Person field.

See this:

Qlikview Cookbook: Date Grouping http://qlikviewcookbook.com/recipes/download-info/date-grouping/

for an example of flexible classification of dates or numbers.

In my opinion, data islands and triggers should be a last resort. Creating dimensions properly linked into the model is almost always the best way.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Lixt Box with Ranges of Values

swuehl — Fri, 19 Feb 2016 19:23:23 GMT

Rob, you're right of course, using the NumberOfMonthsSinceLastIncident to link the tables would be probably better (unless you have only as many persons as distinct NumberOfMonthsSinceLastIncident values).

We can also use a distinct load to remove duplicates. Given that there are just about 50 distinct NumberOfMonthsSinceLastIncident values, the table would be quite small.

Brian, it's of course up to you which approach you want to follow.

Using a list box like described could be a bit confusing, because it will show a selection (like 'ALL'), even when the user has changed the selection in the field NumberOfMonthsSinceLastIncident inbetween. Thus the information shown may be outdated (and list boxes are commonly looked at as representing a linked field in your data model).

Re: Lixt Box with Ranges of Values

Fri, 19 Feb 2016 20:24:45 GMT

Thank you both. This gives me some ideas. I should clarify that the NumberOfMonthsSinceLastIncident field wouldn't be its own listbox. The users would only select by means of the ranges. It would be confusing to have both.