topic Dynamic field selection on trigger button in QlikView

Dynamic field selection on trigger button

Thu, 04 Mar 2010 15:57:12 GMT

Hi,

I want to write a macro, which selects by pushing a trigger button in the field "Customers" all customers with "sales > 10".
I know how to select via bookmark in a macro, but is it possible to do this dynamic?

regards,
corleone

Dynamic field selection on trigger button

blaise — Thu, 04 Mar 2010 16:07:23 GMT

Customers field: Customer

Sales field: Sales



sub Sel10SalesCustomer
 ActiveDocument.ClearAll
 set objCust = ActiveDocument.Fields("Customer")
 set objSales = ActiveDocument.Fields("Sales")
 objSales.Select ">10"
 objCust.SelectPossible
 objSales.Clear
end sub

Dynamic field selection on trigger button

Thu, 04 Mar 2010 16:18:43 GMT

thx 4 answer! short question about this. for my app "sales" is not a field, it's an calculated number from 2 fields.....so objSales.Select ">10" will not work! any idea?

Dynamic field selection on trigger button

blaise — Thu, 04 Mar 2010 16:29:45 GMT

move the calculation to the script.

Cant really come up with an alternative solution. Maybee it can be solved by creating a straight table with customer and your sum calculation, when having the macro loop over the sum field and check if the sum if >10, then fetch the customer dim in the table and ToggleSelect the custom din value to the Customer field. This sounds like a horrible solution though 🙂

Dynamic field selection on trigger button

Thu, 04 Mar 2010 16:42:36 GMT

ok thx i will try!

Dynamic field selection on trigger button

blaise — Thu, 04 Mar 2010 16:58:49 GMT

I got a bit curious on the loop cell values in straight table, so I developed the code for it. My straight table has one dimension, "customer" and one expression. If your table has more dimensions and/or expressions you should change the colIter to match your table (for my example the colIter is 0 for the dimension and 1 for the expression, as seen in the table.GetCell() function).


 sub Sel10SalesCustomer
 ActiveDocument.ClearAll
 set objCust = ActiveDocument.Fields("customer")
 set table = ActiveDocument.GetSheetObject( "CH01" ) 'change to your ID
 Customer = ""
 cellCustCount = 1
 for rowIter = 1 to table.GetRowCount-1
 set cell = table.GetCell(rowIter,1)
 if cell.Text > 10 then
 set cellCust = table.GetCell(rowIter,0)
 if CellCustCount = 1 then
 Customer = cellCust.Text
 cellCustCount = 2
 else
 Customer = Customer &"|"& cellCust.Text
 end if
 else
 'do nothing
 end if
 next
 custString = split(Customer,"|")
 for i= lbound(custString) to ubound(custString)
 objCust.ToggleSelect custString(i)
 next
end sub

Dynamic field selection on trigger button

Miguel_Angel_Baeyens — Thu, 04 Mar 2010 17:01:45 GMT

Hello,

You may try creating a button as new object and adding the built-in action (right click, "Properties" -> Actions tab) and "Select" -> "Select in Field" and use "> 10" (without quotes). I haven't tested that with more complex expressions but it works, and you need not to create a macro.

Regards

Dynamic field selection on trigger button

wizardo — Thu, 04 Mar 2010 17:03:09 GMT

hmmm

just an idea

what if you make a listbox with this expression:

=aggr(sum( [Field_A] )+sum( [Field_B] ),[Customer])

then you could either have your user type >10 in the listbox's search,

or you could wrap the above expr inside an IF statement and make a basket of selections

for example

IF ( aggr(sum( [Field_A] )+sum( [Field_B] ),[Customer]) > 10 , '> 10' , '< 10')

i think you can take it from here

i try to avoid macros when ever i can 🙂

Mansyno

Dynamic field selection on trigger button

blaise — Thu, 04 Mar 2010 17:10:36 GMT

but macros are so fun, but of course your solution is much better (i always forgets expression-made list boxes)

Dynamic field selection on trigger button

wizardo — Thu, 04 Mar 2010 17:17:44 GMT

hmmm yes somtimes they can be

but figuring out how to do things bypassing macros is even more fun 🙂

i thought using a macro too at first but i tried using Evaluate method, which eventually led me to a macroless solution.

Dynamic field selection on trigger button

Mon, 08 Mar 2010 16:15:22 GMT

ok thx to all i'll try it when i have 9.0 on my machine......still working with 8.5

Dynamic field selection on trigger button

wizardo — Tue, 09 Mar 2010 00:33:24 GMT

hmmm

i see no problem using either mine or Blais's methods in 8.5

hack i think it will even work on version 7.

Mansyno