topic Re: Listbox selection order in QlikView

Listbox selection order

Thu, 09 Feb 2012 20:24:50 GMT

Hi,

I currently have a macro that displays the selected values of a field in a listbox.

set s = ActiveDocument.Fields("DrillField").GetSelectedValues

for i = 0 to s.Count-1
msgbox(s.Item(i).Text)
next

However, no matter the order I select the values in the list box, the array of selected values always comes back the same as the listbox's sort order. Is there a way to retrieve the selected values of the field in the order that they were selected?

Thanks.

James

Re: Listbox selection order

Wed, 15 Feb 2012 18:52:37 GMT

What I'm trying to do is the following: I want to be able to dynamically drill a pivot by adding and removing dimensions. But I need a way for the user to choose the dimension to breakdown the pivot by and the order of the dimensions. To that end, I have a data island that lists the dimensions to drill into, the field name of the dimension and the drill order.

I have the macro able to trigger when an option in the listbox is checked (using Windows checkmark box style). The macro sets all unselected dimensions' drill order to 0 and then attempts to add 1 to the drill order of all selected fields. The theory is that a selected field increments in the order of its selection. So, the first field to be selected will always have the highest Drill Order. The last, the lowest.

sub DrillTo
' Set Drill Order of unselected fields to 0
set p = ActiveDocument.Fields("DrillField").GetAlternativeValues
for i = 0 to p.Count-1
set Result = ActiveDocument.DynamicUpdateCommand("UPDATE * SET DrillOrder = 0 WHERE DrillField='"&p.Item(i).Text&"'")
next

' Increase Drill order for all selected fields
set s = ActiveDocument.Fields("DrillField").GetSelectedValues
for i = 0 to s.Count-1
MsgBox(s.Item(i).Text)
set Result = ActiveDocument.DynamicUpdateCommand("UPDATE * SET DrillOrder = DrillOrder + 1 WHERE DrillField='"&s.Item(i).Text&"'")
next

' Get Selected values in Drill Order
' Add to Pivot using AddDimension()
end sub

This really isn't working though. The second DynamicUpdate doesn't seem to work becaue the listbox which is ordered by DrillOrder and is displaying the value of the field, does not change. Can anyone tell me if there is a better way to do this?

Thank you.

James

Re: Listbox selection order

Anonymous — Thu, 16 Feb 2012 06:11:49 GMT

Hi James,

In macro the listbox dynamic selection always select the value based on the Load order of the listbox.

Regards

Ashok

Re: Listbox selection order

Thu, 16 Feb 2012 13:50:00 GMT

Thanks, Ashok. That explains the order from the listbox. Now, I need to figure out how to get the DynamicUpdate to work.

Re: Listbox selection order

Wed, 22 Feb 2012 20:30:51 GMT

After a lot of banging my head against a wall, my macro is working. I have Qlikview dynamically drilling in a pivot so that the end user just needs to check and/or uncheck any dimensions in any order and the pivot will update to breakdown the numbers in the same order.

Listbox selection order

rwunderlich — Wed, 22 Feb 2012 21:56:52 GMT

That would be an interesting thing to see. Are you willing to post a sample?

-Rob

http://robwunderlich.com

Re: Listbox selection order

Thu, 23 Feb 2012 13:36:56 GMT

The data I'm using is confidential so I can't post the original. Let me see if I can create a mockup example of it.

Listbox selection order

Thu, 23 Feb 2012 14:34:42 GMT

I took the Film Example document and added my Drill table and macro to it. Hopefully, it works for everyone. There are lots of extras that could be added to it such as automatically adjusting the column width when a dimension is added or any other Dimension property that exists for that matter. I would just add a column in the DrillPaths table to store that information and then just adjust the macro to apply it when loaded.

Of course, this currently only works on a single Pivot table but it could be expanded to work on any pivot table and any set of dimensions. If anyone has any suggestions, on how to do this better, please let me know. This was my first macro which was made for my first full project in Qlikview so go easy on me.

http://myweb.dal.ca/jrozee/Films_DynamicDrill.qvw

Thanks.

James

Listbox selection order

jagan — Thu, 23 Feb 2012 14:48:20 GMT

Hi,

Can you try like this

Create a variable like and assign the selected values using the below expression

=Concat(FieldName, ',')

Now, you can get the variable value in macro and do the required.

Hope this helps you.

Regards,

jagan.

Listbox selection order

Thu, 23 Feb 2012 14:59:31 GMT

I decided to use an array of fields object which makes it easier to search for duplicates.

Listbox selection order

jagan — Thu, 23 Feb 2012 15:06:19 GMT

Hi,

There is no need to eliminate duplicates manually. You can use

=Concat(DISTINCT FieldName, ',')

Regards,

Jagan.

Listbox selection order

Thu, 23 Feb 2012 15:23:01 GMT

Is there a way to remove a single value from the concatenated string? Would you use something like a replace with an empty string? That would certainly simplify the code.

Listbox selection order

rwunderlich — Thu, 23 Feb 2012 15:44:45 GMT

Interesting. I like it. You may want to turn off "Allow Pivoting" in the Presentation properties. If I drag a dimension and them make a selection I lose my dragged change.

-Rob

Listbox selection order

Thu, 23 Feb 2012 15:50:33 GMT

Good point. I'll disable that. Thanks.