topic Re: Sort via Macro question in QlikView

Sort via Macro question

qlikplaut — Wed, 18 Sep 2013 08:47:52 GMT

Hello,

I am using a Macro to allow a button to sort a table using the first dimension.

Sub SortChart

Set ch = ActiveDocument.GetSheetObject("CH01")

ch.SortBy 0 '' this line specifies the expression to sort by

Set p = ch.GetProperties

Set dims = p.Dimensions

'' Dimensions start by 0

'' 0 = Disable sort

'' 1 = Sort ascending

'' -1 = Sort descending

i = dims.Item(0).SortCriteria.SortByNumeric

If i = 1 Then

dims.Item(0).SortCriteria.SortByNumeric = 1

Else

dims.Item(0).SortCriteria.SortByNumeric = -1

End If

ch.SetProperties p

End Sub

When I have 2 dimensions in the table, I would like this macro to sort both of them, first sort using the first dimension and after using the second.

I can do that from Chart Properties-> Sort, but I need to do it over this macro.

Any ideas?

Thank you in advance,

Bogdan

Re: Sort via Macro question

Gysbert_Wassenaar — Wed, 18 Sep 2013 09:10:48 GMT

dims.Item(0) is the first dimension. If you want to sort another dimension add another if-then-else block for the second dimension using dims.Item(1). Or add them in the same block if you want to sort all the dimensions in the same order.

Re: Sort via Macro question

qlikplaut — Wed, 18 Sep 2013 09:22:15 GMT

Hello,

Thank you for your response. Add another dim. to the macro as you suggested, it will sort the table but the primary sorting option will remain the second dimension. I need to sort primarily from the dim0 and secondarily from dim1. I find it easier to explain with examples:

Here is what I want to achieve:

dim0	dim1
a	1
a	2
a	3
b	1
b	2
b	3

Here is what it does now:

dim0	dim1
a	1
b	1
a	2
b	2
a	3
b	3

Bogdan

Re: Sort via Macro question

marcus_sommer — Wed, 18 Sep 2013 11:28:01 GMT

You could change the order in which you applied this sorting or separate them in multiple executions - but perhaps you need only another SortCriteria - see this example from APIGuide.qvw:

rem set load order sort for all fields in chart Mychart

set chart=ActiveDocument.GetSheetObject("CH01")

set Prop = chart.GetProperties

set vars = Prop.Dimensions

for i = 0 to vars.Count-1

vars.Item(i).SortCriteria.SortByAscii = 0

vars.Item(i).SortCriteria.SortByLoadOrder = 1

chart.SetProperties Prop

- Marcus

Re: Sort via Macro question

qlikplaut — Wed, 18 Sep 2013 12:49:01 GMT

Thank you for your reply Marcus.

Might be sloppy, but I managed to make it work, here is what I used:

Sub SortChart

Set ch = ActiveDocument.GetSheetObject("CH01")

ch.SortBy 1 '' this line specifies the expression to sort by

Set p = ch.GetProperties

Set dims = p.Dimensions

'' Dimensions start by 0

'' 0 = Disable sort

'' 1 = Sort ascending

'' -1 = Sort descending

i = dims.Item(1).SortCriteria.SortByNumeric

If i = 1 Then

dims.Item(1).SortCriteria.SortByNumeric = -1

Else

dims.Item(1).SortCriteria.SortByNumeric = 1

End If

ch.SetProperties p

Set ch = ActiveDocument.GetSheetObject("CH01")

ch.SortBy 0 '' this line specifies the expression to sort by

Set p = ch.GetProperties

Set dims = p.Dimensions

i = dims.Item(0).SortCriteria.SortByNumeric

If i = 1 Then

dims.Item(0).SortCriteria.SortByNumeric = -1

Else

dims.Item(0).SortCriteria.SortByNumeric = 1

End If

ch.SetProperties p

End Sub