weird requirement,please can someone help me out with this issue???????

[#1]

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

[#2]

Hi all,

Please can anyone help with this above issue????????????

[#3]

Hi Sahana,

I do not understand your requirements.

Would it be possible to give us an example .qvw?

[#4]

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??

[#5]

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

[#6]

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

[#7]

I understand. Let me look at it and provide you with a possible solution.

[#8]

hi,

Any solutions for the above issue??????????????

[jagan]

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.

[Anonymous · Accepted solution]

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.