Listbox selection order

[#1]

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

[#2]

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

[Anonymous]

Hi James,

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

Regards

Ashok

[#4]

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

[#5]

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.

[rwunderlich]

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

-Rob

http://robwunderlich.com

[#7]

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.

[#8]

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

[jagan]

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.

[#10]

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