topic Re: Sorting a Field in QlikView

Sorting a Field

azimabadi — Wed, 27 Jun 2012 08:41:44 GMT

HI All,

I have a small table :

LOAD * Inline [
FieldA, FieldB, FieldC
  100, B, 5
  200, E, 4
  300, A, 3
  400, D, 2
  500, C, 1
];

and i have a simple macro :

 SUB testSort
  set fld = ActiveDocument.Fields("FieldA")
  set fldsrt = fld.GetDefaultSortCriteria 
  fldsrt.SortByAscii = 0
  fldsrt.SortByFrequency = 0
  fldsrt.SortByLoadOrder = 0
  fldsrt.SortByNumeric = 0
  fldsrt.SortByState = 0
  fldsrt.SortByExpression = 1
  fldsrt.Expression.v = "Ord(FieldB)"
  fld.SetDefaultSortCriteria fldsrt
  set val = fld.GetPossibleValues
  for i=0 to val.Count-1
    msgbox(val.Item(i).Text)
  next
END SUB

i expect(need) to get the message box in the order of FieldB :

300, 100, 500, 400, 200

but this code dosn't go as i run.

i have attached the sample file too.I will appreciate any help.

Best Regards

Peyman

Re: Sorting a Field

azimabadi — Wed, 27 Jun 2012 09:07:42 GMT

It seems that in macro, the sort order ist original "Load Order" and setting the sortCriteria affects only the objects that put on interface after runing of macro.

Re: Sorting a Field

deepakk — Wed, 27 Jun 2012 09:13:45 GMT

hi Peyman,

Instead of taking the value from the field, try to take the value from a field. It will be much easier.

I hope the attach sample helps you out.

Deepak

Re: Sorting a Field

azimabadi — Wed, 27 Jun 2012 09:24:34 GMT

Thank you Deepak,

It's really intersting way. I mark your answer as "Helpful" so other people can see my problem and give suggestions. When i get no other and better answer i will mark it as "Correct Answer".

Best Regards

Peyman

Re: Sorting a Field

jagan — Wed, 27 Jun 2012 09:54:51 GMT

Hi Peyman,

You can also do this by using Dual() in script. Check attached file for solution.

Regards,

Jagan.

Re: Sorting a Field

azimabadi — Wed, 27 Jun 2012 10:06:43 GMT

Thank you Jagan,

I Think this a better way, because i don't neet to defeine a order Expression.

Best Regards

Peyman