Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to set a variable in the script of a load.
Variable Name
vCurrentQuarterNum
Expression
max({1<[Fiscal - Flag Current Period]={'Y'}>}[Fiscal - Quarter #])
What would be the syntax for this?
Thanks!
You can try this, for example. It's much easier to reuse the filter in other expressions.
SUB ExpandMacro(vVariableName)
LET vVariableName = Text(Replace(vVariableName, '@', '$'));
END SUB
SET vFilterFlag =
[Fiscal - Flag Current Period]={'Y'};
SET vCurrentQuarterNum =
max({1<$(vFilterFlag)>}[Fiscal - Quarter #]);
Call ExpandMacro(vCurrentQuarterNum);
Let vCurrentQuarterNum = 'your expression';
Hi,
can you share your data structure ?
what is the values on the [fiscal -quarter] ?
Hi,
OP want a solution in the script, which is equivalent to his expression.. and set analysis don't work in the script..
he will need to use Max() function in a group by Clause with a WHERE or peek() function
This issue I am running into there is that the expression has a field that has single quotes in it and it is causing the syntax to fail
[Fiscal - Flag Current Period]={'Y'}
SUB ExpandMacro(vVariableName)
LET vVariableName = Text(Replace(vVariableName, '@', '$'));
END SUB
SET vCurrentQuarterNum =
max({1<[Fiscal - Flag Current Period]={'Y'}>}[Fiscal - Quarter #]);
Call ExpandMacro(vCurrentQuarterNum);
you can purge your field in the script like this:
purgechar([Fiscal - Flag Current Period],chr(39))
and your expression will work
You can try this, for example. It's much easier to reuse the filter in other expressions.
SUB ExpandMacro(vVariableName)
LET vVariableName = Text(Replace(vVariableName, '@', '$'));
END SUB
SET vFilterFlag =
[Fiscal - Flag Current Period]={'Y'};
SET vCurrentQuarterNum =
max({1<$(vFilterFlag)>}[Fiscal - Quarter #]);
Call ExpandMacro(vCurrentQuarterNum);
a number: 1-4
This worked! Thanks so much!