topic Re: an exp with many variables can be put in load script???? need help please??? in QlikView

an exp with many variables can be put in load script???? need help please???

Tue, 10 Jun 2014 06:09:23 GMT

Hi all,

Please can someone help me to solve the below issue?????????????please????

I have attached a file in which I have a table with the last column called as 'net',this expression consists of many variables in it.

I want to put this expression in the main script/load script and rename it as a field A''so that my future calculations involving this field A would be much easier.Can we do this???????

please can someone help??

the variables are updated in a macro .if you check in the edit module the function is 'Black 76'

I think we need to call macro and then put this exp below renaming it as required.Is this the process??else please guide me.

Re: an exp with many variables can be put in load script???? need help please???

Tue, 10 Jun 2014 07:17:32 GMT

Hi all ,

after searching in the community,I got a solution of how we pass the macro function in load script ,I did the same in my script,the function has no error but the exp which I try to rename after that shows invalid expression message while running.

my function in macro that is used in script is Black 76:its used in last tab,at the end in script.

Please can anyone help me out??

Re: an exp with many variables can be put in load script???? need help please???

tresB — Tue, 10 Jun 2014 07:26:21 GMT

It is expecting the parameters that have been declared in the function definition.

Re: Re: an exp with many variables can be put in load script???? need help please???

Tue, 10 Jun 2014 07:40:45 GMT

hi tresesco

.. I have actually commented the msg box in edit module so that it doesnt ask for the parameters.but still I get below error as 'invalid expression.' for the exp that I have given below the macro function in script.

Don understand where its going wrong .What ever I am doing is it the right way ?? or another way is present???

Please can you guide me?

Re: an exp with many variables can be put in load script???? need help please???

tresB — Tue, 10 Jun 2014 07:47:45 GMT

See, when you write macro like:

function Black76(CallPutFlag,F,X,T,r,v)

.....

That means the function calculates on six input values(for CallPutFlag,F,X,T,r,v) and returns one. When you want to get some output from the function, it should actually been called in the script like:

LET Black76 = Black76(Value1, Value2,.....Value6);

You are missing those. Hope this helps.

Re: an exp with many variables can be put in load script???? need help please???

Tue, 10 Jun 2014 07:52:50 GMT

ok understood, thanks for the response,

but it should not just take 1 single line of value,there are several rows that will be have to taken and calculated .like the column called option_chk in my table is the same as this exp in script below the function.Here each row has its own values getting calculated.Hence in such a scenerio how can we do this??

Re: an exp with many variables can be put in load script???? need help please???

tresB — Tue, 10 Jun 2014 08:30:38 GMT

You can do that by using the function in the Load script like I have shown here :StrCmp Function

Re: an exp with many variables can be put in load script???? need help please???

Tue, 10 Jun 2014 09:01:25 GMT

Hi,

I couldn't end up with the result as I wanted ,but thanks a lot for your help and time.

Will see how it works out,will do some more trials .

Re: Re: an exp with many variables can be put in load script???? need help please???

Wed, 11 Jun 2014 04:06:52 GMT

Hi Tresesco,

I tried the strcmp() function you had attached but I am getting one error as shown in the file attached.

Can you please check my script ones if possible and let me know if I am following the correct steps ,please????

I have attached my qvw as well,and my function script is in the tab called funct

Re: an exp with many variables can be put in load script???? need help please???

tresB — Wed, 11 Jun 2014 04:47:22 GMT

You are passing a parameter called call_put which is not there the source.

Re: an exp with many variables can be put in load script???? need help please???

Wed, 11 Jun 2014 04:56:25 GMT

Hi,

Thanks for the response,

Yes,but call_put =if(xoptiontype_='PUT','p','c') because the first parameter to be passed to the function is a string which should be either 'c ' or 'p' according to PUT/CALL value in xoptiontype_ so I gave that condition and renamed this as a new field called call_put and trying to pass the same.

Is this wrong else any other way to do this??? can you please guide me??

Re: an exp with many variables can be put in load script???? need help please???

tresB — Wed, 11 Jun 2014 05:05:05 GMT

The calculated field can't be refered in the same load statement like that. Rather try preceding load like:

black_76:

Load

Black76(call_put,strike_price1,strike_price,time_to_expiry,'0.105',Volatility_Amended) as new_option_fuction

;

load

(Expiry_-Today())/365 as time_to_expiry,

(if(xoptiontype_='PUT','p','c')) as call_put

Resident data_;

Re: Re: an exp with many variables can be put in load script???? need help please???

Wed, 11 Jun 2014 06:05:08 GMT

Hi Tresesco,

Thanks for reminding ,had forgotten that part of script ,but I have corrected the same.Now the reload took place successfully but the field renamed which is the outcome of the function Black76 ie new_option_function doesn't show any result.You can have a look at the table box in sheet 6 of the file attached.

Am I still going wrong somewhere?? please request help.

Re: an exp with many variables can be put in load script???? need help please???

tresB — Wed, 11 Jun 2014 06:22:06 GMT

In your function definition one msgbox is there, just comment it out.

function Black76(CallPutFlag,F,X,T,r,v)

Dim d1,d2,op

d1 = (Log(F / X) + (v ^ 2 / 2) * T) / (v * Sqr(T))

'msgbox d1

d2 = d1 - v * Sqr(T)

....

Re: Re: an exp with many variables can be put in load script???? need help please???

Wed, 11 Jun 2014 06:54:58 GMT

Hi Tresesco,

Thanks a lot,the values are coming now for new_option_function. ,I am getting to know little more about macros now, but had a doubt,cant we use this field obtained from the function black76 for other calculations in the script??

something like :

if(buy_sell='BUY',new_option_function-premium1,premium1-new_option_function)

as asoff_payoff

and then I try to pull this asoff_payoff next to the new_option_function in the table???? I am presently not getting values for this

.Is this not possible???

You can refer the file attached if required.

Re: an exp with many variables can be put in load script???? need help please???

tresB — Wed, 11 Jun 2014 07:06:43 GMT

Hi Sahana,

You can, and it is being calculated as well. Take the field asoff_payoff in a list box, you would find the values. In your straight table use expression:= Sum(asoff_payoff), values would get populated.

, hope this helps you close the issue.

Re: Re: an exp with many variables can be put in load script???? need help please???

Wed, 11 Jun 2014 07:33:10 GMT

Hi hi I myself want to close this very fast Tresesco,and I am able to proceed almost till the end with your generous help which you have provided all the way.

The asoff_payoff value actually doesn't match.I calculated it in script as well as in table also.The one calculated in table comes up but that calculated in script doesn't. If I put sum(Sum(asoff_payoff) in the table very huge value comes which is wrong .

As soon as this is resolved will close this asap .

Re: an exp with many variables can be put in load script???? need help please???

tresB — Wed, 11 Jun 2014 07:45:20 GMT

Your asoff_payoff field is not associated to any other tables. Rewrite the last part of your code as :

black_76:

load

if(buy_sell='BUY',new_option_fuction-premium1,premium1-new_option_fuction) as asoff_payoff

;

load *,

[Trade ID] as trade,

Position_ID as pos,

strike_price as markt,

strike_price1 as strik,

Volatility_Amended as vol_amd,

xoptiontype_ as cp,

call_put as call_put1,

premium as premium1,

Expiry_ as exp,

time_to_expiry as time_to_expiry1,

Black76(call_put,strike_price1,strike_price,time_to_expiry,0.105,Volatility_Amended) as new_option_fuction

Resident data_;

// **********Following section should be commented out like

//as_off:

//load

//if(buy_sell='BUY',new_option_fuction-premium1,premium1-new_option_fuction) as asoff_payoff

//Resident black_76;

Re: an exp with many variables can be put in load script???? need help please???

Wed, 11 Jun 2014 08:13:45 GMT

Hi thanks a lot Tresesco.

finally I reached the end

as your wish will close it off.

Thanks again.

Re: an exp with many variables can be put in load script???? need help please???

tresB — Wed, 11 Jun 2014 08:19:16 GMT

You are welcome ! And happy that I could help you reach the end.