Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have created a variable called vCurrentOP which in this case is equal to 11. Later in the load, I want to compare the operating period (field named Period) to the vCurrentOP variable and if Period is greater then enter 'F' and if less then enter 'P' in a field called Future.
Here is the formula in the load:
if(Right([FYOP],2)*1 > ('$(vCurrentOP)'), 'F', 'P') as Future
The problem is everything results in 'F'.
As mentioned, the variable is created earlier in the load and I used this formula to create it:
Let vCurrentOP = '=max(CP)'
The field CP is currently equal to 11 which is the current operating period. In the actual app, I can use the variable and it has the correct value.
I should end up with all periods less than or equal to 11 having a P and those greater than 11 having a F in the field Future.
We can't use the field directly in your LET Expression as stated below .
Let vCurrentOP = '=max(CP)'
you need to use the PEEK to get the value in field CP
something like this
TEMP:
LOAD MAX(CP) as T_CP
RESIDENT CP_TABLE
GROUP BY CP_PRIMARY_KEY;
LET vCurrentOP= Peek('T_CP',0,'TEMP');
DROP TABLE TEMP;
Later you can have the above variable in wherever you need in the script.
if(Right([FYOP],2)*1 > ('$(vCurrentOP)'), 'F', 'P') as Future
Hope this Helps.
Thanks
CY
Have you confirmed that the variable has the value in it that you expected?
In the app, if I use the varaible in a KPI or a formula it works.
$(vCurrentOP) in the app results in 11, which is the correct answer.
$(vCurrentOP)+1 results in 12, so I think it's working.
Hi
May be change variable to this
Let vCurrentOP = max(CP);
We can't use the field directly in your LET Expression as stated below .
Let vCurrentOP = '=max(CP)'
you need to use the PEEK to get the value in field CP
something like this
TEMP:
LOAD MAX(CP) as T_CP
RESIDENT CP_TABLE
GROUP BY CP_PRIMARY_KEY;
LET vCurrentOP= Peek('T_CP',0,'TEMP');
DROP TABLE TEMP;
Later you can have the above variable in wherever you need in the script.
if(Right([FYOP],2)*1 > ('$(vCurrentOP)'), 'F', 'P') as Future
Hope this Helps.
Thanks
CY
That didn't work. Using Let vCurrentOP = max(CP); in the load results in the variable not working in the app in addition to not working in the load. In the app, $(vCurrentOP)+1 now results in 1 rather than 12 like it should if it was working.
That worked! Thanks for your help!