Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Macro to loop on a field order by another field

Hello,

I need to do a macro which loop on a list on field value in a certain order as example below:

MyFieldMySortField
A1gA1
fefz2
adfgh3
1sdz4


I want to select in the order of MySortField the value in the field MyField.

Is it possible to do in macro with getPossibleValues with others functions without use a list box?

If not, how can I do the same thing in looping on a list box object containing field Value of MyField order by MysortField ? What macro function use to do this in using directly this graphic object.

In the second case, I have seen a little problem : sort by expression in my list box don't work on "gray" value (exclude of possible values list). they are all at the end of the available values which are correctly sorted.

Could you help me?

Thanks.

6 Replies
Anonymous
Not applicable
Author

You have to loop by the MySortField, but use MyField as "output".  Something like this:

sub macro
set sf=ActiveDocument.Fields("MySortField").GetPossibleValues
set mf=ActiveDocument.Fields("MyField")

for i=0 to sf.Count-1
msgbox (mf.GetPossibleValues.item(i).text)
next
end sub

Not applicable
Author

Thanks.

I solve it in adding a select for each of my SortFieldValue before select first possible value of MyField (only one value by sortFieldValue selection).

I suppose the sort is automaticaly done on the field type (numeric or text) ? Or it's the line order in the QlikView table?

Regards.

Anonymous
Not applicable
Author

I think the sort is taken from the field sort order on the document properties.  Which is, by default, numeric for numbers and text for others.

Larry_Aaron
Employee
Employee

user5674,

I am having the same issue where I need to load an alpha numeric field (Field #1) and have it load/sorted by a numeric sort field (Field #2).  The sort order of (Field #1) is unique to each person's ID number (Field #3).  I have tried what I believe is your solution of using a .Select, but can't get the sort order to change.  Can you share a little code.

Not applicable
Author

Hello,

Sure:

MySortField : the field use for sorting

MySelectField : the field sorted

A QlikView inline table like this

MySortField,MySelectField

1, aaaa

2, d466+

3, arfegg

..., ...

Set MySortField = ActiveDocument.Fields("MySortField")

Set MySortFieldOrder = MySortField.GetPossibleValues

nbSelect = MySortFieldOrder.Count

If nbSelect>0 Then

for valIter = 0 to nbSelect-1

  vSortFieldValue=MySortFieldOrder.Item(valIter).text

  IF vSortFieldValue<>"" THEN

   MySortField.Select vSortFieldValue

   ActiveDocument.GetApplication.WaitForIdle

   set selectionFieldValues = ActiveDocument.Fields(MySelectField).GetPossibleValues

   vMySelectFieldValue=selectionFieldValues.Item(0).Text

   ActiveDocument.Fields(MySelectField).Select vMySelectFieldValue

  End IF

Next

End IF

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

A simpler approach may be to leverage the sortorder of the QV concat() function.

x = ActiveDocument.Evaluate("concat(chr(34) & MySelectField & chr(34), ',', MySortField)")

list = Eval("Array(" + x + ")")

For Each item in list

  msgbox item

Next

-Rob

http://robwunderlich.com