9 Replies Latest reply: May 12, 2009 11:29 PM by tnorton RSS

    RemoveDimension by field name

    tnorton

      I have a pivot table and I'm trying to remove a field by it's name. I found help from other posts, but I am unable to make it work correctly. Can someone help please?

      Macro:

       

      sub

       

      addfields105

      Set

       

      Tabl = ActiveDocument.GetSheetObject("SD02")

      Set

       

      Prop = Tabl.GetProperties

      Set

       

      Dims = Prop.Dimensions

      for

       

      i = 0 to Dims - 1

      if

       

      Prop.Dimensions(i).Title.v = "Sub_ProductID"end if

      next

      end

       



      sub



        • RemoveDimension by field name
          Neil Miller

          Are you getting an error when running this macro?

          It looks like the problem is with your for statement. In your macro, Dims is an object. You don't want the Dims object in your for statement, you want the count of the memebers of the Dims object.

          Use:

          for i = 0 to Dims.Count - 1

          That worked for me using some sample data. Keep in mind that if you change the Title of the Dimension to something more user friendly, you will need to search for that instead of the field name. For example Prop.Dimensions(i).Title.v = "Product ID".

            • RemoveDimension by field name
              tnorton

              Here is what I have and still no error:

              sub

               

              addfields105

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               

               



               

               





               

               

              Set

               

               

              Tabl = ActiveDocument.GetSheetObject("SD02"

              )

              Set

               

               

              Prop = Tabl.GetProperties

              set

               

               

              dims = prop.Dimensions

              for

               

               

              i = 0 to Dims.Count - 1

              if

               

               

              Prop.Dimensions(i).Title.v = "Sub_ProductID"

              then

              end

               



               

              if

              next

              end

               



               

              sub



               

               





                • RemoveDimension by field name
                  Neil Miller

                  Well, you do know that you have nothing in between the if statements, so if that Title is encountered, nothing happens, correct? I used the following sub and got a pop-up saying Found It! I changed the Object ID and the Dimension Title to reflect my app.

                  sub addfields105

                  Set Tabl = ActiveDocument.GetSheetObject("CH25")

                  Set Prop = Tabl.GetProperties

                  set dims = prop.Dimensions

                  for i = 0 to Dims.Count - 1

                  if Prop.Dimensions(i).Title.v = "Week" then
                  MsgBox("Found It!")
                  end if
                  next

                  end sub

                  What I did to test this out on my system was to set a variable to the Title of one of the Dimensions. That way, I could see what QlikView was calling it. For your case, I would figure out what Dimension number Sub_ProductID is and then before your for statement, add:

                  ActiveDocument.Variable("vTestTitle").SetContent Prop.Dimensions(1).Title.v

                  Where 1 is the Dimension number you are looking for. Then you can make sure that the string your looking for matches the string that QlikView has.

                    • RemoveDimension by field name
                      tnorton

                      Ok. I spoke too soon. I'm still having issues finding the dimension after adding it. Here is my latest code. Is there a good way to paste code into this forum without having to replace all the tabs with spaces? The statement below chokes on the portion in bold. It says "Invalid Procedure call or argument"

                      sub addfields105
                      set btn = ActiveDocument.GetSheetObject("BU105")
                      set propb = btn.GetProperties
                      set pt = ActiveDocument.GetSheetObject("SD02")
                      Set propt = pt.GetProperties
                      set Dims = propt.Dimensions
                      if (propb.Text.v = "Add Product") then
                      propb.Text.v = "Remove Product"
                      btn.SetProperties propb
                      pt.AddDimension "Sub_ProductID"
                      propt.Dimensions(Dims.Count).Title.v = "ProductID"
                      pt.SetProperties propt
                      else
                      for i = 0 to Dims.Count - 1
                      propb.Text.v = "Add Product"
                      btn.SetProperties propb
                      if propt.Dimensions(i).Title.v = "ProductID" then
                      pt.RemoveDimension i
                      end if
                      next
                      end if
                      end sub

                        • RemoveDimension by field name
                          Neil Miller

                          I haven't found a good way to paste code into the forum. I usually paste it into Notepad before pasting it here to get it as plain text. I believe that may also keep your tabs.

                          I think the problem with that line is that the Count starts at 0, so there is no Dimension with the number Dims.Count. Dims.Count - 1 would be your highest numbered dim.

                            • RemoveDimension by field name
                              tnorton

                              The Dims.Count - 1 is working but it is removing the wrong field, because the code is naming the wrong one. I believe the add dimension quit working now.

                              Code:

                              Rem add/remove field from Subscriber Detail
                              sub addfields105
                              set btn = ActiveDocument.GetSheetObject("BU105")
                              set propb = btn.GetProperties
                              set pt = ActiveDocument.GetSheetObject("SD02")
                              Set propt = pt.GetProperties
                              set Dims = propt.Dimensions
                              if (propb.Text.v = "Add Product") then
                              propb.Text.v = "Remove Product"
                              btn.SetProperties propb
                              pt.AddDimension "Sub_ProductID"
                              set Dims = propt.Dimensions
                              propt.Dimensions(Dims.Count - 1).Title.v = "ProductID"
                              pt.SetProperties propt
                              else
                              for i = 0 to Dims.Count - 1
                              propb.Text.v = "Add Product"
                              btn.SetProperties propb
                              if propt.Dimensions(i).Title.v = "ProductID" then
                              pt.RemoveDimension i
                              end if
                              next
                              end if
                              end sub

                              :End

                                • RemoveDimension by field name
                                  Neil Miller

                                  Weird. If you comment out the three lines after the AddDimension, the Dimension will be added successfully, but not renamed. If you leave those three lines in, the Dimension will not be added, but the highest numbered Dimension will be renamed ProductID.

                                  I found a work around (I have no idea why it works this way and not the other). Do the renaming in a separate Sub.

                                  sub addfields105
                                  set btn = ActiveDocument.GetSheetObject("BU24")
                                  set propb = btn.GetProperties
                                  set pt = ActiveDocument.GetSheetObject("CH25")
                                  Set propt = pt.GetProperties
                                  set Dims = propt.Dimensions
                                  if (propb.Text.v = "Add Product") then
                                  propb.Text.v = "Remove Product"
                                  btn.SetProperties propb
                                  pt.AddDimension "Sub_ProductID"

                                  'propt.Dimensions(Dims.Count - 1).Title.v = "ProductID"
                                  'pt.SetProperties propt

                                  Call newSub
                                  else
                                  for i = 0 to Dims.Count - 1
                                  propb.Text.v = "Add Product"
                                  btn.SetProperties propb
                                  if propt.Dimensions(i).Title.v = "ProductID" then
                                  pt.RemoveDimension i
                                  end if
                                  next
                                  end if
                                  end sub

                                  sub newSub
                                  set btn = ActiveDocument.GetSheetObject("BU24")
                                  set propb = btn.GetProperties
                                  set pt = ActiveDocument.GetSheetObject("CH25")
                                  Set propt = pt.GetProperties

                                  if (propb.Text.v <> "Add Product") then
                                  set Dims = propt.Dimensions
                                  propt.Dimensions(Dims.Count - 1).Title.v = "ProductID"
                                  pt.SetProperties propt
                                  end if
                                  END SUB

                                  The only thing odd I could find on the original is that you define Dims twice. Removing one didn't solve the problem. Other than that, the two methods should be equivilent.

                                  • RemoveDimension by field name
                                    tnorton

                                    Here is what I ended up with:

                                    sub addfields105
                                    set btn = ActiveDocument.GetSheetObject("BU105")
                                    set propb = btn.GetProperties
                                    if (propb.Text.v = "Add Product") then
                                    propb.Text.v = "Remove Product"
                                    set pt = ActiveDocument.GetSheetObject("SD02")
                                    pt.AddDimension "Sub_ProductID"
                                    set propt = pt.GetProperties
                                    btn.SetProperties propb
                                    set Dims = propt.Dimensions
                                    propt.Dimensions(Dims.Count - 1).Title.v = "ProductID"
                                    pt.SetProperties propt
                                    else
                                    set pt = ActiveDocument.GetSheetObject("SD02")
                                    set propt = pt.GetProperties
                                    set Dims = propt.Dimensions
                                    for i = 0 to Dims.Count - 1
                                    propb.Text.v = "Add Product"
                                    btn.SetProperties propb
                                    if propt.Dimensions(i).Title.v = "ProductID" then
                                    pt.RemoveDimension i
                                    end if
                                    next
                                    end if
                                    end sub

                                    The only issue I have left is that when the field is added it wants to be horizonal instead of a vertical column. I'll have to look for the property to see if I can set it.

                                    Thanks for all your help. If you happen to know a quick fix for my last problem. Please feel free. Thanks again.

                            • RemoveDimension by field name
                              tnorton

                              Got it. Thanks for the help. I found that I wasn't looking for the correct property since I hadn't customized the header names yet. Thanks agin.