Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
mov
Esteemed Contributor III

Macro to loop on a field order by another field

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

Macro to loop on a field order by another field

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.

mov
Esteemed Contributor III

Macro to loop on a field order by another field

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.

Employee
Employee

Macro to loop on a field order by another field

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

Macro to loop on a field order by another field

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

Re: Macro to loop on a field order by another field

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

Community Browser