topic Re: Applying Filter Using VBScript in QlikView

Applying Filter Using VBScript

pkelly — Mon, 26 Nov 2018 12:44:12 GMT

sub ApplyDistanceFilter

	vdf = getVariableString("varDistanceFrom")
	vdfield =getVariableString("varDistanceFromSiteField")
	vdi = getVariableString("varDistanceInside")
	
	IF vdi = 1 Then	'	Inside
		 ActiveDocument.Fields(vdfield).Select " <" & vdf
	ELSE			' Outside
		 ActiveDocument.Fields(vdfield).Select " >" & vdf
	END IF
end sub

GetVariableString Function

function getVariableString(varName)
	set v = ActiveDocument.Variables(varName)
	getVariableString = v.GetContent.String
end function

Trying to apply a filter via VBScript using the above.

The script has a few variables...

vdf = Distance from site - numeric (populated using a slicer
vdfield = Field I want to filter on - there are four possibles (we have four sites)
vdi = Inside (1) or Outside (0) cvf

I cannot get this to work - hoping it is just syntax somewhere.

Any assistance greatly appreciated.

Paul

Re: Applying Filter Using VBScript

marcus_sommer — Mon, 26 Nov 2018 13:25:43 GMT

Maybe it doesn't like the set-statement within the function - just try it with:

function getVariableString(varName)
	getVariableString = ActiveDocument.Variables(varName).GetContent.String
end function

and reading the variable-value in this way I would probably not use an extra function for it.

Beside this I'm not sure if I would use a macro at all because it (the direct selection of fieldvalues) could also be implemented with actions - and it might be also an alternatively not to select anything else to use these variables (or maybe fieldvalues from an island-table) within set analysis expressions.

- Marcus

Re: Applying Filter Using VBScript

pkelly — Mon, 26 Nov 2018 13:39:31 GMT

Thanks Marcus - already have this working with Set Analysis...

=IF(varDistanceFromSite = 'None', SUM(sin_Sales),

	IF(varDistanceFromSite = 'Glasgow' AND varDistanceInside = 1, SUM({<cst_MilesFromGlasgow = {"<= $(varDistanceFrom)"} >} sin_Sales),
	IF(varDistanceFromSite = 'Glasgow' AND varDistanceInside = 0, SUM({<cst_MilesFromGlasgow = {">= $(varDistanceFrom)"} >} sin_Sales),
		
	IF(varDistanceFromSite = 'Poole' AND varDistanceInside = 1, SUM({<cst_MilesFromPoole = {"<= $(varDistanceFrom)"} >} sin_Sales),
	IF(varDistanceFromSite = 'Poole' AND varDistanceInside = 0, SUM({<cst_MilesFromPoole = {">= $(varDistanceFrom)"} >} sin_Sales),
		
	IF(varDistanceFromSite = 'SDC' AND varDistanceInside = 1, SUM({<cst_MilesFromSDC = {"<= $(varDistanceFrom)"} >} sin_Sales),
	IF(varDistanceFromSite = 'SDC' AND varDistanceInside = 0, SUM({<cst_MilesFromSDC = {">= $(varDistanceFrom)"} >} sin_Sales),
		
	IF(varDistanceFromSite = 'Stonehouse' AND varDistanceInside = 1, SUM({<cst_MilesFromStonehouse = {"<= $(varDistanceFrom)"} >} sin_Sales),
	IF(varDistanceFromSite = 'Stonehouse' AND varDistanceInside = 0, SUM({<cst_MilesFromStonehouse = {">= $(varDistanceFrom)"} >} sin_Sales))))))))))

Issue I have is that I am displaying data on a Bullet Chart with a Map of the UK and it is very clunky.

The map does not zoom in so thought I could get round this with filtering.

The change you have suggested are having an impact - the map is filtering but as soon as I try to apply < or > nothing happens..

If vdi = 1 Then	'	Inside
		ActiveDocument.Fields(vdfield).Select " <" & vdf
Else			' Outside
		ActiveDocument.Fields(vdfield).Select " >" & vdf
End If

Thanks

Re: Applying Filter Using VBScript

marcus_sommer — Mon, 26 Nov 2018 13:53:50 GMT

A selection with < or > worked usually well by a numerical field. This means that either your field isn't numeric or your vdf variable isn't treated as a number, for example if it contained a formatting like a comma as thousand- or decimal delimiter.

- Marcus

Re: Applying Filter Using VBScript

pkelly — Mon, 26 Nov 2018 14:05:30 GMT

There is a decimal delimiter - distances are to 1dp.

How do I get round this?

Re: Applying Filter Using VBScript

marcus_sommer — Mon, 26 Nov 2018 14:12:04 GMT

You might round the number with something like int() or you could use replace() to replace the comma with a dot.

- Marcus

Re: Applying Filter Using VBScript

pkelly — Mon, 26 Nov 2018 14:22:10 GMT

Thanks for all your help so far...

If vdi = 1 then	'	Inside
	ActiveDocument.Fields(vdfield).select vdf
Else			' Outside
	ActiveDocument.Fields(vdfield).select  vdf
End If

If I use the above with my slider set to 50, it works as I have a customer(s) exactly 50 miles away.

Also changed the slider as I have a customer who is 11.5 miles away and it filtered okay on 11.5.

This suggests to me that the number format is okay.

Therefore, I think it is syntax..have tried...

If vdi = 1 then	'	Inside
	ActiveDocument.Fields(vdfield).select < vdf
Else			' Outside
	ActiveDocument.Fields(vdfield).select  > vdf
End If

And...

If vdi = 1 then	'	Inside
	ActiveDocument.Fields(vdfield).select " <" & vdf
Else			' Outside
	ActiveDocument.Fields(vdfield).select  " >" & vdf
End If

With no success.

Re: Applying Filter Using VBScript

pkelly — Mon, 26 Nov 2018 14:30:20 GMT

Got it...

If vdi = 1 then	'	Inside
	ActiveDocument.Fields(vdfield).select "<" & vdf
Else			' Outside
	ActiveDocument.Fields(vdfield).select  ">" & vdf
End If

The ApiGuide had a space before < and >.

Took that out and it works.