Using a variable in the load script

[nikonmike]

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. 

[rittermd]

Have you confirmed that the variable has the value in it that you expected?

[nikonmike]

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.

[Gabriel]

Hi

May be change variable to this

Let vCurrentOP = max(CP);

[ychaitanya · Accepted solution]

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

[nikonmike]

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.

[nikonmike]

That worked!  Thanks for your help!