Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all ,
I have a peculiar issue please help me in resolving the same.
I have a table as shown in the file attached.the field to be considered is market value .I want the output in the below manner .
1)my market value needs to be rounded to 1 decimal place ,consider the minimum,+3 and -3 to increase its limit.
2)ie: each position id should have all the market values in it and buy calls and same should repeat for other is's as well.so that when I plot a graph where market value being x axis and position id as y axis I would like t get it in a line graph.Is this possible can someone please help.
position is | market value_1 | buycall |
162648 | 76.8 | -0.15 |
162648 | 76.9 | -0.15 |
162648 | 77 | -0.15 |
162648 | . | -0.15 |
162648 | . | -0.15 |
162648 | 89.9 | -0.15 |
162649 | 76.8 | -0.15 |
162649 | 76.9 | -0.15 |
162649 | 77 | -0.15 |
162649 | . | -0.15 |
162649 | . | -0.15 |
162649 | 89.95 | -0.15 |
159949 | 76.8 | -0.52 |
159949 | 76.9 | -0.52 |
159949 | 77 | -0.52 |
159949 | . | -0.52 |
159949 | 89.9 | -0.52 |
Hello, Sahana.
I did it using this code in the script:
MinMaxMarketValue:
Load
Round(Min(market_value)-3, 0.1)*10 as MinMarketValue,
Round(Max(market_value)+3, 0.1)*10 as MaxMarketValue
Resident data_;
Let vMinMarketValue = Peek('MinMarketValue');
Let vMaxMarketValue = Peek('MaxMarketValue');
Drop Table MinMaxMarketValue;
MarketValues:
Load
(RecNo() + $(vMinMarketValue) - 1)/10 as market_value_1
AutoGenerate $(vMaxMarketValue) - $(vMinMarketValue) + 1;
Outer Join (MarketValues) Load Distinct
position_id
Resident data_;
Let vMinMarketValue =;
Let vMaxMarketValue =;
Notice I used the ROUND function. You may want to change it to FLOOR or CEIL depending of your requirements.
Attached is a qvw with your data showing the solution.
Regards.
Hi all,
Please can anyone help with this above issue????????????
Hi Sahana,
I do not understand your requirements.
Would it be possible to give us an example .qvw?
hi Marius,
I think my question was confusing ,sorry,I will break it up so that we can resolve it step by step.
in the application attached I have a column named market_value.Now how would I create another column named market_value1 where it should also include min( market_value)-3 and max(market_value) +3 as the new min and max values but in increments of 0.1 from the min to max values.
market_value_=(min(market_value)-3),market_value,(max(market_value)+3)... in increments of 0.1
eg :if my present market value is ranging from 75.8 to 82.25 then my result should be
market_value1=(min(market_value)-3),market_value,(max(market_value)+3)..
market_value1=72.8,72.9,73,73.1,73.2..............75.8......82.25....85.1,85.25)
we can round to 1 decimal place like from 72.8 till 82.2.
Is it better to understand now??ones this is solved will move to the next part.
Please can you help me out??
Hi Sahana,
Thanks. But why do you want to keep all those values in one field?
Why can't we simply create one field with one minimum market value and one field with the maximum market value. And then you can do where market_value >= market_value_min and <= market_value_max?
Regards,
Marius
Hi Marius,
I do not want to create this new field for comparison.my end result should be :
Each position is across each market value1 with market value scale differing with 0.1.And hence I would want a line graph chart,where market_value would be on my x axis and my position_is in my y axis.So I can look at each trades line over all the market values.I need to arrive at this result
Hence my plan was create the new filed market_value first and then join it with the rest .
something like the below table:there are 2 eg tables ,you can refer the same.
presently data is like below table.
position is | market value1 |
a | 72.5 |
b | 72.6 |
c | 72.7 |
d | 75.2 |
e | 72.5 |
f | 72.6 |
g | 72.7 |
h | 80.2 |
i | 72.5 |
j | 72.6 |
k | 72.7 |
l | 82.2 |
m | 72.5 |
n | 72.6 |
o | 72.7 |
p | 82.2 |
and the result should be like the one below.
position | market value1 |
a | (min)72.5 |
a | 72.6 |
a | 72.7 |
a | (max)82.2 |
b | (min)72.5 |
b | 72.6 |
b | 72.7 |
b | (max)82.2 |
c | (min)72.5 |
c | 72.6 |
c | 72.7 |
c | (max)82.2 |
d | (min)72.5 |
d | 72.6 |
d | 72.7 |
d | (max)82.2 |
I wish you understood now... please help me out
I understand. Let me look at it and provide you with a possible solution.
hi,
Any solutions for the above issue??????????????
Hi Sahana,
Try this script
Temp:
LOAD *
INLINE [
Position, MarketValue
1, 5.2
1, 6.3
2, 6.8
2, 3.5
3, 6.5
3, 3.5
] ;
Data:
LOAD
Position,
Min(MarketValue) - 3 AS MinValue,
Max(MarketValue) + 3 AS MaxValue
RESIDENT Temp
Group By Position;
FinalData:
LOAD
*,
MinValue + ((IterNo() -1)*0.1) AS CalcValue
RESIDENT Data
WHILE MinValue + ((IterNo() -1)*0.1) <= MaxValue;
Drop Tables Data, Temp;
Regards,
jagan.
Hello, Sahana.
I did it using this code in the script:
MinMaxMarketValue:
Load
Round(Min(market_value)-3, 0.1)*10 as MinMarketValue,
Round(Max(market_value)+3, 0.1)*10 as MaxMarketValue
Resident data_;
Let vMinMarketValue = Peek('MinMarketValue');
Let vMaxMarketValue = Peek('MaxMarketValue');
Drop Table MinMaxMarketValue;
MarketValues:
Load
(RecNo() + $(vMinMarketValue) - 1)/10 as market_value_1
AutoGenerate $(vMaxMarketValue) - $(vMinMarketValue) + 1;
Outer Join (MarketValues) Load Distinct
position_id
Resident data_;
Let vMinMarketValue =;
Let vMaxMarketValue =;
Notice I used the ROUND function. You may want to change it to FLOOR or CEIL depending of your requirements.
Attached is a qvw with your data showing the solution.
Regards.