topic Re: Straight Table Conditional Column Display in QlikView

Straight Table Conditional Column Display

Mon, 07 Oct 2013 16:50:55 GMT

I have the attached QVW that I'm working on. Multiple selections can be made in the HANDLERID ListBox and any rows which *contain* those values should be returned. The Expressions ListBox allows the user to choose which columns will be displayed in the table. If the user selects only the Handler column to be displayed, my example is working correctly. If the user chooses any other column to be displayed along with the Handler column, my example falls apart... all rows are being returned - not just those which match the selections in the HANDLERID ListBox. I understand that I could put the same logic in the remaining columns as I did for the Handler column within the table itself, however, this example is a very small sample of what I need to accomplish. In the end there will be around 20 ListBoxes which will need to function like the HANDLERID ListBox and there will be somewhere around 75-100 filters in the Expressions ListBox. Is there any other way I can account for this functionality that I need?

Re: Straight Table Conditional Column Display

Anonymous — Mon, 07 Oct 2013 17:18:21 GMT

Kerri,

You can solve it in one place - in the dimension. Instead of using field ID as dimension, use calculated dimension, like this:

aggr(only({<HANDLER={$(vSearch)}>} ID),ID)

And, check "suppress when value is null). Table will show ID rows only if there is HANDLER value (if this is what you need).

Regards,

Michael

Re: Straight Table Conditional Column Display

Mon, 07 Oct 2013 17:20:35 GMT

But what will I do when HANDLER is really 20 separate filters instead of just the 1?! This seems like a very complex solution in that scenario.

Re: Re: Straight Table Conditional Column Display

Anonymous — Mon, 07 Oct 2013 17:31:19 GMT

It is somewhat complex, but it works. And, notice that the expressions now are much simpler, no any conditions. The differrence is that all the complexity is in only one place - in the calculated dimension.

Regards,

Michael

Re: Straight Table Conditional Column Display

Mon, 07 Oct 2013 17:55:42 GMT

I agree that it works, but with 20+ filters, is this really the "cleanest" way to handle the requirement? How will the script look with 20 filters, not just HANDLER??

Re: Straight Table Conditional Column Display

Anonymous — Mon, 07 Oct 2013 18:05:13 GMT

(Trying to guess, not sure I understand this correctly)

If there are multiple search fields, you can create a variable for each - vSearch2, vSearch3 (better rename to someting more intuitive) - and include all in the same calculated dimension:

aggr(only({<HANDLER={$(vSearch)}, FILTER2={$(vSearch2), FILTER3={$(vSearch3), ... FILTER20={$(vSearch20)}>} ID),ID)

I'm not saying it is the best possible solution. Let's see if somebody comes up with a cleaner one.

Regards,

Michael

Re: Straight Table Conditional Column Display

Mon, 07 Oct 2013 18:38:41 GMT

I've tried this solution and it works when there is a selection. But when there is no selection made (all data to be shown), it falls apart. There are a great number of rows missing in this scenario.

Re: Straight Table Conditional Column Display

Anonymous — Mon, 07 Oct 2013 18:56:05 GMT

I could've tried to guess again, but it is not very effective... Can you upload an example?

Re: Straight Table Conditional Column Display

Mon, 07 Oct 2013 19:42:42 GMT

It is the previous example that you uploaded that I was using. If you use that example, and make no selections, those records with have no values in the Handler field are missing from the table.

Re: Straight Table Conditional Column Display

Anonymous — Mon, 07 Oct 2013 19:56:39 GMT

I was sure that this was the requirement - don't show rows with missing Handler value. Apparently I misunderstood you.

Can you explain on an example, e.g. this row should be shown (and why) but it is missing?

Or maybe there should be a separate condition for "no selection"? That is, all HANDLERID selected is not the same as none selected (?)

Re: Straight Table Conditional Column Display

Mon, 07 Oct 2013 21:28:10 GMT

The requirement is that if no selection is made, all rows should be shown (as is the standard with QlikView). Otherwise only rows which contain the selected value(s) should be shown. The issue I was having earlier was that if the user wanted to show all possible columns and made a selection for Handler, for some reason all rows were being returned. However if the user wanted to show only the Handler column with selected Handler values, then the rows returned were correct. Hope that makes sense.

Sent from my mobile device

Re: Re: Straight Table Conditional Column Display

Anonymous — Tue, 08 Oct 2013 15:36:56 GMT

I guess you want the table to work as in your example when no Handler selections made, but as in my example when there are Handler selections. If this is true, try my example with a little different calculating expression, see the third table

Because of the alternate sets, I don't have a "clean" condition of "no selection" status. Using an additional variable. And, for the same reason, if select all values it works the same as no selections.

The problem is that there is no relaible way to count selections in alternate state - see this idea and vote: http://community.qlik.com/ideas/2340

Regards,

Michael

Re: Straight Table Conditional Column Display

Tue, 08 Oct 2013 15:44:23 GMT

Thanks for the workaround. I actually worked around it by coding in my LOAD statements for a <NULL> value to be able to be selected n my listbox as I needed this functionality anyways. This worked to allow all rows to show when no selection was made.