topic RemoveDimension by field name in QlikView

RemoveDimension by field name

Tue, 12 May 2009 21:31:16 GMT

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

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

end

sub

RemoveDimension by field name

Tue, 12 May 2009 21:49:20 GMT

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

Tue, 12 May 2009 23:30:21 GMT

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

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

then

end

sub

RemoveDimension by field name

Tue, 12 May 2009 23:46:52 GMT

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

Tue, 12 May 2009 23:48:28 GMT

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.

RemoveDimension by field name

Wed, 13 May 2009 01:59:27 GMT

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

Wed, 13 May 2009 02:49:05 GMT

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

Wed, 13 May 2009 02:59:11 GMT

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

Wed, 13 May 2009 03:27:55 GMT

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

Wed, 13 May 2009 03:29:20 GMT

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.