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.
udaya_kumar
Valued Contributor

Sort individual field in straight table by descending order using macro

Hi,

i want to sort individual field separately as descending order by using macros (VBScript)

how i can do that?

Tags (2)
1 Solution

Accepted Solutions

Re: Sort individual field in straight table by descending order using macro

Hi,

There may be some misunderstanding here. Expressions are results, not values, and cannot be sorted. What you do can do is sort a dimension using an expression, so, for example, in a chart that displays all your customers and their corresponding current year sales, you can sort the dimension Customer by the Sum(Amount) of sales ascending or descending, although you double click on the expression to sort.

Add the following code:

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(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


1 will refer to the first expression, 2 to the second and so on.

Hope that helps.

Miguel

6 Replies
chandashok
Contributor III

Re: Sort individual field in straight table by descending order using macro

Hi

     Please see the attached file and you can change the qv file based on your requirement

     Hope this will helpful for  your problem

Regards

Ashok

Sort individual field in straight table by descending order using macro

Hi,

Check this thread and the file attached to see a working example on a macro to sort in a chart.

Hope that helps.

Miguel

udaya_kumar
Valued Contributor

Sort individual field in straight table by descending order using macro

Hi Miguel and chand,

thanks for the reply,

i need to sort the expression field by descending order in the straight table, not the dimensions.

Re: Sort individual field in straight table by descending order using macro

Hi,

There may be some misunderstanding here. Expressions are results, not values, and cannot be sorted. What you do can do is sort a dimension using an expression, so, for example, in a chart that displays all your customers and their corresponding current year sales, you can sort the dimension Customer by the Sum(Amount) of sales ascending or descending, although you double click on the expression to sort.

Add the following code:

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(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


1 will refer to the first expression, 2 to the second and so on.

Hope that helps.

Miguel

Not applicable

Re: Sort individual field in straight table by descending order using macro

Hi Miguel and Chand,
Dimension is getting sorted based on the expression value. But it is toggling between ascending order and descending order. I want to set it to descending order only. Can it be done ??
I m using this macro
Sub SortChart
Set ch = ActiveDocument.GetSheetObject("CH05")
ch.SortBy 1
Set ch = ActiveDocument.GetSheetObject("CH15")
ch.SortBy 1
Set p = ch.GetProperties
Set dims = p.Dimensions
i = dims.Item(0).SortCriteria.SortByNumeric= -1
ch.SetProperties p
End Sub

Re: Sort individual field in straight table by descending order using macro

Hi,

You are using two charts to sort by but the dimension is only in one of them. Something seems to be wrong there, or at least make little sense, since the "dims" variable stores the dimension of the CH15 chart, and not the CH05. Maybe is that driving the macro interpreter crazy?

Regards.

Miguel

Community Browser