Re: Variable from Input Box into Load Script - Qlik Community

jason_nicholas · ‎2017-06-22

We use a static exchange rate from £ to $ which is adjusted every 6 months rather than the daily market exchange rate. I'd like to have an input box on the visualization to update this value to be clear which rate is being used. When I use the method below, a change in this input box does not adjust the resulting value, as I am hoping it would.

My load script starts with:

LET varExchange1=1.39;

LET varExchange2=1.39;

LET varExchange= IF(Month(Date)<='Jun',$(varExchange1),$(varExchange2)

I want the function to look at the date and use the value for varExchange1 in the first half of the year and the value for varExchange2 for the second half of the year anywhere I use varExchange. I need to use varExchange within the load script itself, rather than as a expression in a visualization.

I also want to have the value (1.39 here) be adjustable from the main page on the visualization. For instance, next month, if they come back and say the new rate is 1.35, I want to be able to enter it in the input box for varExchange2 and have that change the load script.

( I expect I could make the change in the script directly and just have a text box on the main page showing the value for each exchange rate so a user can confirm, but a front end function to make this change would be preferable in my situation.)

Is it possible to have a front end input change the load script variable? When I load the script as it exists, I can look at the input total and the output total and see that it is calculating the exchange correctly, but if I change the 1.39 to any other number, the output total does not change. If I reload with the new value in the input box, it just resets to 1.39.

Can I even do what I am trying here?

vishsaggi · ‎2017-06-22

Did you actually implemented or tried the approach you described above? Are you facing any errors ?

jason_nicholas · ‎2017-06-22

‌The input box set to the variable has no impact on the results.

I have field list of the original number and another showing the one after the exchange rate. I can see that the exchange calculation is being done because field shows a value 1.39 times the other. But if I change the variable to something else, the calculated field does not change. If I edit the input box and hit refresh, the value returns to 1.39, as listed in the script.

vishsaggi · ‎2017-06-22

Is it possible to share your app please?

settu_periasamy · ‎2017-06-23

May be try this..

Let Date=Today();


if((not IsNull($(varExchange1)) and $(varExchange1)<>$(vTempExchange1)) or (not IsNull($(varExchange1)) and $(varExchange2)<>$(vTempExchange2))) THEN
LET varExchange= IF(Month(Date)<='Jun',$(varExchange1),$(varExchange2));


ELSE
LET varExchange1=1.39;
LET vTempExchange1=$(varExchange1);
LET varExchange2=1.39;
vTempExchange2=$(varExchange2);
LET varExchange= IF(Month(Date)<='Jun',$(varExchange1),$(varExchange2));
ENDIF

jason_nicholas · ‎2017-06-23

I could use a little help unpacking this to make sure I understand it correctly. Let me try to read your script in plain language to build a picture. Please let me know if I have it wrong or if I am missing anything key.

Let Date=Today()- is not necessary because the calculation decision should be based on the date of the entry, not the current calendar date.

The next part says:

If varExchange1 (or 2) IS NOT null---- I think this is checking for an entry on the visualization front page. There should always be an entry there, so if I have this correct, varExchange1 will not ever be null.

And if varExchange does not equal vTempExchange---- I don't really understand the place of vTempExchange1 here. Where does vTempExchange1 (or 2) get set so that it could potentially be different from varExchange1?

Then choose a varExchange version value based on the month in the date field

If varExchange1 IS null, then set a specific value listed in the load script and make the vTempExchange entries equal that value as well- as I noted, I won't ever have a null result here, whether I can enter it on the visualization front page or if I have to put it in the script.

Assuming I understand this correctly, should I be able to just copy/paste your script and replace my three line LET script above?

Which variable(s) do I want to set in the input box on my visualization front page?

Am I safe eliminating LET Date=Today() if I want to reference the entry date rather than today's date?

settu_periasamy · ‎2017-06-23

Hi,

See my answers in Red

Jason Nicholas wrote:

I could use a little help unpacking this to make sure I understand it correctly. Let me try to read your script in plain language to build a picture. Please let me know if I have it wrong or if I am missing anything key.

Let Date=Today()- is not necessary because the calculation decision should be based on the date of the entry, not the current calendar date.    - This is just for demonstration purpose

The next part says:

If varExchange1 (or 2) IS NOT null---- I think this is checking for an entry on the visualization front page. There should always be an entry there, so if I have this correct, varExchange1 will not ever be null.

- Preventing Purpose (Suppose if there no value entered), if you are sure, then you can remove this

And if varExchange does not equal vTempExchange---- I don't really understand the place of vTempExchange1 here. Where does vTempExchange1 (or 2) get set so that it could potentially be different from varExchange1?    - In this part , we are storing the previous entry value into temp variable. So, if you change something in the front page, this condition get true(), then the new entry value will be store into your variable1.

Then choose a varExchange version value based on the month in the date field

If varExchange1 IS null, then set a specific value listed in the load script and make the vTempExchange entries equal that value as well- as I noted, I won't ever have a null result here, whether I can enter it on the visualization front page or if I have to put it in the script.

Assuming I understand this correctly, should I be able to just copy/paste your script and replace my three line LET script above? - I can say 'Yes'. or you can load the static exchange value as first time script loading, then you can comment that line. (Anyhow the variable is already created, you can change it in the front page). The values comes automatically into your script.

Which variable(s) do I want to set in the input box on my visualization front page?

               - Which variable you want to change (I think varExchange1 and varExchange2)

Am I safe eliminating LET Date=Today() if I want to reference the entry date rather than today's date?

           - Yes, Already mentioned.

jason_nicholas · ‎2017-06-23

Thank you for your response. I will try this and mark your answer Correct once it works. It sounds like this is the solution.