How to use variables in formulas?

Bobby

Forums

Hi Brian,
I have recorded a makro which includes a criterion that looks like this:
task.AddExtraction dbName, "", "(betrag <= @FieldStatistics(""BETRAG"";11)-2*@FieldStatistics(""BETRAG"";18)) .OR. (BETRAG>=-@FieldStatistics(""BETRAG"";11)+2*@FieldStatistics(""BETRAG"";18))"
In order to make it more flexible, I want to define the field name "BETRAG" as variable (part of an array). The name of the variable is Fields(3). It should look like this:
'task.AddExtraction dbName, "", "(Fields(3)<= @FieldStatistics(""Fields(3)"";11)-2*@FieldStatistics(""Fields(3)"";18)) .OR. (Fields(3)>=-@FieldStatistics(""Fields(3)"";11)+2*@FieldStatistics(""Fields(3)"";18))"
However, it does not work. How can I replace a fixed field name by a variable in a formula?
Thanks, Bobby
 
 
 

Brian Element Fri, 09/16/2016 - 07:33

Hi Bobby,

What you were missing is that an equation that is held in an equation editor is actually just a string that is fed to IDEA.  In order to add variables to this string you must close the double quote (") and use the ampersand (&) to put the equation together.  So everytime you want to add the Fields(3) variable to the equation you have to close the double quotes and use the & to include the variable as part of the string.  In your equation the equation would be looking for Fields(3) instead of the variable it is holding.  I have posted two ways to do this.  The first is just closing the quotes and using the & to attach the variable.  The second way uses the Chr(34) to represent the two double quotes (""), sometimes the second way is easier to read as it doesn't screw-up the editor's colour coding.

Hopefully this helps you out.

Brian

Note that I didn't test this out so I might have misplace an & or " in there.

task.AddExtraction dbName, "", "(" & Fields(3) & "<= @FieldStatistics(""" & Fields(3) & """;11)-2*@FieldStatistics(""" & Fields(3) & """;18)) .OR. (" & Fields(3) & ">=-@FieldStatistics(""" & Fields(3) & """;11)+2*@FieldStatistics(""" & Fields(3) & """;18))"

task.AddExtraction dbName, "", "(" & Fields(3) & "<= @FieldStatistics(" & Chr(34) & Fields(3) & Chr(34) & ";11)-2*@FieldStatistics(" & Chr(34) & Fields(3) & Chr(34) & ";18)) .OR. (" & Fields(3) & ">=-@FieldStatistics(" & Chr(34) & Fields(3) & Chr(34) & ";11)+2*@FieldStatistics(" & Chr(34) & Fields(3) & Chr(34) & ";18))"

 

Bobby Tue, 09/27/2016 - 02:57

Hi Brian,
thank you very much for your help. I have tested the second alternative since it is indeed easier to read and it works out well.
All the best
Bobby