Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nikonmike
New Contributor II

Using a variable in the load script

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. 

1 Solution

Accepted Solutions
ychaitanya
Contributor III

Re: Using a variable in the load script

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

6 Replies
rittermd
Honored Contributor

Re: Using a variable in the load script

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

nikonmike
New Contributor II

Re: Using a variable in the load script

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.

gabsus04
Valued Contributor II

Re: Using a variable in the load script

Hi

May be change variable to this

Let vCurrentOP = max(CP);

ychaitanya
Contributor III

Re: Using a variable in the load script

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
New Contributor II

Re: Using a variable in the load script

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
New Contributor II

Re: Using a variable in the load script

That worked!  Thanks for your help!