topic Re: How to sort with the help of macro! in QlikView

How to sort with the help of macro!

Fri, 14 Oct 2011 09:48:22 GMT

Hi All

We are generating the Straight table with the help of macro. In that one of the field we wants to sort it by "Load Order: Original". For example if the "Dimension=Month" then sort it by "Load Order: Original". How can I achieve this with the help of macro.

Following is the sample of code which we were using in the macro for setting up the number properties. I want to similar kind of coding to add to sort the Dimension: "Month" by "Load Order: Original".

  SET objNumberProperties = objPivotTableProperties.Expressions.Item(intAddedMeasureCount + intLoop).Item(0).Data.ExpressionVisual.NumberPresentation
  objNumberProperties.Fmt = "#,##0.000"
  objNumberProperties.nDec = 0
  objNumberProperties.Thou = ","
  objNumberProperties.Type = 11

Hope some one can help me out on this soon as it is little urgent.

Thanks

Attitude

How to sort with the help of macro!

kaushiknsolanki — Fri, 14 Oct 2011 10:33:25 GMT

Hi,

I am not in my office today, so will not be able to give you tested answer.

Pls try this.,

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

Regards,

Kaushik Solanki

How to sort with the help of macro!

Fri, 14 Oct 2011 11:25:52 GMT

Hi Kaushik

For your information, the scenario is...

We are creating the straight table with the help of macro. In that what happens is user selected Dimension and expression will be passed in and straight table is created with the help of macro. So the properties such as position and number properties is controlled in the macro script.

The script which I have a added above is just the part of the script which we are using to set the NumberProperties. So something in a similar way I would like to sort as mentioned in my post.

Seeing your script looks like you are passing the GetSheetObject("CH01") but in my scenario we are not sure about the GetSheetObject. Also I have included in my macro but it didn't work. I am sorry! If you get some time can you please look into it. If not can you please give me some solution once you are available in office.

Before you digin please let me know if you need anymore details from me.

Thanks

Attitude

How to sort with the help of macro!

Fri, 14 Oct 2011 11:39:21 GMT

Hi Kaushik

I added your code along with my macro script and looks like it is sorting as expected. But the only concern is I am setting the set GetSheetObject manually. How we can pass the sheet object dynamically whenever the macro has been called. If we get to know this then I think it will work fine.

chart=ActiveDocument.GetSheetObject("CH01")

If you have time please look into it and give some solution please.

Thanks

Attitude

Re: How to sort with the help of macro!

IAMDV — Fri, 14 Oct 2011 22:58:23 GMT

Hi,

How many sheetobjects do you have in the sheet? If you have more than one similar type of object then how will QV decides on which object you want the sorting? Or do you want to sort all the sheet objects? Please can you explain in detail?

Thanks - DV

Re: How to sort with the help of macro!

Sat, 15 Oct 2011 16:33:33 GMT

Hi DV

Thanks for your reply. Once the user selects the sheet dimension and expression in the front end they will select the button which calls the macro to create the straight table. So obiviously the number of straight table(object) is depends on how times the user clicks on the button.

If fact as you said I want to sort all the sheet object. Those many time user clicks on the button those many sheet objects will be created. I want to sort all those sheet object.

Hope my requirement are understandable now.

Thanks

Attitude

How to sort with the help of macro!

kaushiknsolanki — Mon, 17 Oct 2011 10:27:43 GMT

Hi,

If you are creating a chart with a button click and macro then you can do this way.

set chart = ActiveDocument.Sheets("SH01").CreateStraightTable

//Straight table dimension and expression code

// then
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

Regards,

Kaushik Solanki

Re: How to sort with the help of macro!

Mon, 17 Oct 2011 11:25:45 GMT

Hi Kaushik

Thanks for the code! But again I think you are hardcoding the sheet object("SH01"). That is what my concern it.

The previous code which you have given was working fine but the only issue with that was

("SH01") was hard coded.

Thanks

Attitude

Re: How to sort with the help of macro!

IAMDV — Mon, 17 Oct 2011 11:30:28 GMT

Hi Mate,

Have you checked my post? I have used ActiveDocument instead of hard coding the Sheets or SheetObjects. Please see the below code which exports all the Objects in the ActiveDocument. And you dont need to give the sheet name nor SheetObject name. Please use the below idea and integrate with your code.

Sub Export_All_Chart_Images()

For i = 0 To ActiveDocument.NoOfSheets - 1

Set MySheet = ActiveDocument.GetSheet(i)

MyCharts=MySheet.GetSheetObjects

For X =lbound(MyCharts) to ubound(MyCharts)

ActiveDocument.Sheets(i).Activate

msgbox("Chart: " & MyCharts(X).GetObjectId)

Set obj = ActiveDocument.GetSheetObject(MyCharts(X).GetObjectId)

IF Obj.GetObjectType >= 10 AND Obj.GetObjectType =< 16 Then

obj.ExportBitmapToFile "C:\Users\deepak.vadithala\Desktop\All\Test\Images_" & Right(MyCharts(X).GetObjectId, 4) & ".bmp"

End if

Next

End Sub

Good luck!

Cheers - DV

How to sort with the help of macro!

kaushiknsolanki — Mon, 17 Oct 2011 11:56:53 GMT

Hi,

Try

set chart=ActiveDocument.ActiveSheet.CreateStraightTable

Regards,

Kaushik Solanki

Re: How to sort with the help of macro!

Anonymous — Fri, 28 Apr 2017 13:17:06 GMT

Hi Deepak,

Macro is working fine to add a straight table in the active document - active sheet. But is there any way we can add expression and dimension in the macro .