Dollar sign expansion: Evaluate expression outside of LOAD statement

[MichaelW]

Hi,

I know how to use a $-sign expansion to emulate a "custom function" within a LOAD statement. However, I want to evaluate an expression saved in the variable while being outside of a LOAD statement and that doesn't seem to work. Here is the general concept of what I would like to achieve:

SET myVar = if(1, 'true', 'false') ;
TRACE $(=myVar); // Should say 'true' but is null

Is there any way to make the Dollar sign expansion evaluate the expression within the variable?

This is the first time this great community didn't already have an answer for me (thank you!) and I would be glad for any ideas!

Michael

PS: In case you are interested, here is why I'm  doing this: I am writing a script where "WHERE"-statements can be dynamically configured by using variables. Currently I have done this and it works fine:

SET whereMyCondition = WHERE match(PLANT, 1000) ;

MYTABLE:
LOAD * INLINE [
PLANT, ORDER
1000, 1234
1100, 5678
] $(whereMyCondition);

But I want to reuse the whereMyCondition in another load together with whereMyOtherCondition (whereMyCondition AND whereMyOtherCondition) and then I have trouble placing the "WHERE" keyword.

My idea was the following:

SET whereMyCondition = match(PLANT, 1000) ; // Omit WHERE
SET whereMyOtherCondition = match(ORDER, 1234) ; // Omit WHERE

// Create WHERE dynamically if there is a condition
// and leave it out completely if there is none
SET whereClause = if(len($1) > 0, 'WHERE ' & $1, '');

MYOTHERTABLE:
NOCONCATENATE
LOAD * INLINE [
PLANT, ORDER
1000, 1234
1100, 5678
] $(whereStatement( $(whereMyCondition) &' AND '& $(whereMyOtherCondition) ));

But this doesn't work because I cannot get $(whereStatement) to evaluate to a string. 

[sunny_talwar]

How about if you use this

LET myVar = if(1, 'true', 'false') ;
TRACE $(myVar); // Should say 'true' but is null

[MichaelW]

Hi Sunny,

thank you and sorry, I realize my example was not specific enough. Yes, that does work. But I need to evaluate the expression when it is called, not when it is declared. The reason is that, in the example for my real-world application, the variable "whereClause" should serve as a general wrapper function for creating where statements so it cannot be static.

[sunny_talwar]

Would you be able to provide a script which isn't working?

[MichaelW]

Of course, in the attached qvf-file there are my three examples from the initial post combined

[marcus_sommer]

I think two things are wrong. First you specify whereClause as your variable but then you call whereStatement but I assume it's just caused from your various trials on the matter. More important as this is that you tried to execute an expression within the variable: var1 & ' AND ' & var2.

I don't want to say that this completely impossible - maybe by wrapping it with an evaluate() - but I suggest to build the whole where-string in beforehand just by adding one part to another and counting your number of different conditions to set the appropriate AND and OR.
A nesting von multiple variables with or without parameters leads easily to a nightmare in regard to expressions in it and the various quotes and brackets which might be needed for strings and fields and even more important to possible commas within them (which are always treated as parameter-delimiter and which couldn't be masked in any way). Also within the script is only a single $-sign expansion possible and not a double one like: $(=$( ... In your provided example it will probably not be relevant but if your real cases are more complex it could become easily a showstopper.

I could imagine that I would rather use a load-statement to load one or several tables (maybe from Excel) to build the where-clause by various aggregations and/or preceeding loads with/without interrecord-functions like peek() and using just one variable to read it from the finished table. I think you won't need more efforts for this way and data on which you want to filter your loadings must be in each case specified anywhere - and therefore why not in a table instead of in variables.

Beside this if your filter-data come already from any tables it might be that you don't need that where-clause building on the fly else you could also very effective filter your data with exists() or inner joins or something like that.

- Marcus

[MichaelW]

Thank you for your very thorough answer! You are right, of course, that I ended up confusing the variable names but that it didn't alter the result. I am attaching a revised version.

I only half agree with your second point which is that the problem is due to me trying to execute an expression within the variable. Yes, that surely makes it more complicated, but the problem shows already in the simple example:

SET myVar = if(1, 'true', 'false') ;
TRACE $(=myVar); // Should say 'true' but is null

In The Magic of Variables it is explained that you can get the expression to evaluate before the variable expansion by using an "=" sign. However, I can only get that to work within a load statement and would like to know if it is achievable also outside the load statement.

That said, the alternative you present is will certainly be my plan B if my original idea does not work out. 

One more note about your comment on problems using commas in nested dollar sign expansion: I remember having had that problem when defining the variables using LET and quoting the content. When I switched to using SET (without quotes) I believe the problem has vanished, but I am not 100% sure of it.

[marcus_sommer · Accepted solution]

The logic/syntax of $(=var) respectively $(=$(var)) could be only used within the UI and not within the script regardless if it's within a load or on the outside.

The comma-problem lies primarily not in the declaration of the variable else by calling them within another variable/function. More to it could you find here: variable-with-comma-parameter.

- Marcus

[MichaelW]

Ok, then that is (sadly) not possible. Thank you for your explanations!