topic Re: Group selections in QlikView

Group selections

Mon, 22 Jun 2015 13:32:57 GMT

I'm trying to set up a listbox to have some defined groups of values instead of all the values from a field. In this case I have some support tickets with various statuses, some of the statuses represent closed tickets and others open ones. I want to allow the users to just be able to select 'open' or 'closed' rather than having to multiple select the various statuses for each type.

I've actually managed to get it to work in a way, but it doesn't look great on the report. Here is how I set it up -

Created new listbox

For the field value, selected <Expression>

Out of the many statuses, only 3 represent closed tickets, all the rest are open. So I entered this for the expression - Now

If(match([Status],'Cancelled','Closed','Rejected'),'Closed','Open')

Now in the report, I have my listbox but it lists the word open twice. However whichever one I click on, it highlights both in green and I do get to see all the open tickets in my table that I have. And clicking on closed, which only appears once, it also works fine.

I have many tickets in each status, so could understand if I had the words open and closed repeated many times - once for each ticket - but it's only repeating open once.

Does anyone know why this is happening, or am I doing this the wrong way?

Re: Group selections

marcus_sommer — Mon, 22 Jun 2015 13:37:22 GMT

I would probably do this kind of matching within the script and use then a native field within the listbox.

- Marcus

Re: Group selections

sunny_talwar — Mon, 22 Jun 2015 13:39:02 GMT

What happen when you do this:

=If(match([Status],'Cancelled','Closed','Rejected'),'Closed', If(match([Status], 'Open', 'Open'))

Assuming the fourth option within Status is Open

Re: Group selections

pratap6699 — Mon, 22 Jun 2015 13:51:34 GMT

If(match([Status],'Cancelled','Closed','Rejected'),'Closed','Open') it's perfect

Re: Group selections

Mon, 22 Jun 2015 13:54:19 GMT

Open isn't the fourth status, there are about 6 more that are used for open tickets. I can try it with them all listed in the second part but I was hoping to just say if it isn't a status in the first list then assume it's open.

Re: Group selections

Mon, 22 Jun 2015 13:55:13 GMT

I am thinking I may need to do this - add the open\closed setting when the data is loaded. Would still be nice to understand why it does this though

Re: Group selections

sunny_talwar — Mon, 22 Jun 2015 13:58:55 GMT

Ya I don't know why it would list them twice, but like Marcus said, it would make sense to do it in the script. But just for the curiosity, I would check if listing them out helps or not.

Best,

Sunny

Re: Group selections

Mon, 22 Jun 2015 14:17:26 GMT

I tried this with interesting results. First I tried as suggested with every status listed-

If(match([Status],'Cancelled','Closed','Rejected') ,'Closed'
,If( match([Status],'Test (to be validated)','Description to complete','Delivery','Validated','Standby','Explanation given','New ticket')
,'Open'
,'Open'
) )

which didn't change the results at all. I thought maybe there was a status that it couldn't match, therefore dropping into the last 'else' condition and creating a third option also called 'Open'. So I changed the last 'Open' to 'Unknown', i.e. if it doesn't satisfy any of the previous if conditions.
This results in my listbox containing Closed, Open, Open, Unknown. So I still get Open duplicated but I do also get Unknown. If I select Unknown however nothing changes, it doesn't go green and the results in the tables etc all stay the same. The Open and Closed options work as they were before.

Re: Group selections

sunny_talwar — Mon, 22 Jun 2015 14:23:45 GMT

Do you get the option of selecting the frequency? Can you check if they both appear the same number of time or do they have different appearances (the two opens I mean). Just getting curious as to why this is happening

Re: Group selections

marcus_sommer — Mon, 22 Jun 2015 14:53:16 GMT

Your case is a kind of categorize data which is nearly always better done within the script in additional fields/tables. Your app loading-time and the app size will slightly increase but you could afterwards handle these data as native fields which avoid potential problems and saved a lot of calculation-time in expressions or if you need these logic in calculated dimensions.

If you get strange results by these kind of matching you could create an extra dummy-table on row-level with them maybe per QUALIFY statement to see which status-value gets which match-value. Often it is helpful to use something like this to minimize wrong/missing matches:

match(keepchar(upper(trim(Status)), 'ABCDEFGHIJKLMNOPQRSTUVW'), 'CANCELLED', ...

- Marcus