Need help plzz ..

[sibideepak]

i have to table two tables following:

target:

prod  q1  q2  q3  q4

A       6     8     5     9

price:

prod   start      end      price

A          1          5          100

A          6          10          200

A          11         20         300

A          21         30         400

expected o/p:

prod      q1     q2          q3        q4

A          700    2000    1500      3500

concept:

1.for q1: 6 products then,1-5=100, 5*100=500 ,then remaining 1 is in range 6-10,1*200=200,

now q1=500+200=700

2.for q2: 8 products then,it continuous from q1 ie) 8 will be counted to q1(7-14) then 7-10=4*200=800 ,11-14=4*300=1200

now q2=800+1200=2000

3.for q3: 5 products then ,  it continuous to q1,q2 ie) q3(15-19) ,5*300=1500

4.for q4:9 products then, it continuous to q1,q2,q3 ie) q4(20-28),1*300=300 + 8*400=3200

now q4=3500..

we have to calculate all quarter as single not each quarter has range .

have to implement dynamically...

help needed ..

Thanks ,

Deepak Sibi

[sibideepak]

Hello anbu1984 ,

In this case , if any value that exceeds the give range should be multiplied with the last range price..

ie)

30 is last range if q1 is 35 ..remaining 5 and q2 ,q3 ,q4 should be with last range price range.

Thanks in advance ,

Deepak Sibi

[anbu1984]

Is it possible to add a row to your data like below with end having maximum number? Then you can use the same code

price:

LOAD * Inline [

prod, start,end,price

A,1,5,100

A,6,10,200

A,11, 20, 300

A,21, 30, 400

A,31,999999,400];

[sibideepak]

But it takes more load time and waste of records ....is there any possibilities of some expression..

[anbu1984]

Try this

Target:

load * Inline [

prod,q1,q2,q3,q4

A, 30, 8, 5, 9 ];

NoConcatenate

price:

LOAD * Inline [

prod, start,end,price

A,1,5,100

A,6,10,200

A,11,20,300

A,21,30,400];

Join(Target)

Load prod,(start + IterNo()-1) as start,end, price Resident price while IterNo() <= (end -start +1);

Final:

//Load prod,If( prod <> previous(prod) And q1>=end,(q1-end + 1)*price,If(start<=q1,price)) as sum1,start,end Resident Target order by end desc, start desc;

Load prod,sum(If(prod <> previous(prod) And q1>=end,(q1-end + 1)*price,If(start<=q1,price))) as sum1,

sum(If(prod <> previous(prod) And (q1+q2)>=end,If(q1>=end,q2,q1+q2-end + 1)*price,If(start>q1 And start <=q1+q2,price))) as sum2,sum(If(prod <> previous(prod) And (q1+q2+q3)>=end,If(q1+q2>=end,q3,q1+q2+q3-end + 1)*price,If(start>q1+q2 And start <=q1+q2+q3,price))) as sum3,

sum(If(prod <> previous(prod) And (q1+q2+q3+q4)>=end,If(q1+q2+q3>=end,q4,q1+q2+q3+q4-end + 1)*price,If(start>q1+q2+q3 And start <=q1+q2+q3+q4,price))) as sum4

Resident Target Group by prod order by end desc, start desc;

Drop table price;

Drop table Target;