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

[#2]

Hello Deepak,

Can you please explain a bit for below:

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

How can 8 products be counted as (7-14) ??

It should 8 = 5+3 means 5*100 + 3*200 = 1100. ??

Thanks,

Singh

[sibideepak]

Thanks itsangad,

That I said each quarter does not depend on range ..it is calculated as single for a product...

ie) q1(1-6),q2(7-14),q3(15-19),q4(20-28)...

this is my requirement acutally

[MK_QSL]

Deepak, Kindly elaborate little more... Very confusing question itself..

[#5]

Ok Deepak. I got your requirement. Working on it. Will get back soon.

[sibideepak]

Hello MRKachhiaIMP ,

Can I know where you are missing from the concept ??

If you tell which part I will explain bit more ..

Thanks,

Deepak

[MK_QSL]

No... I read again and understood the concept behind your question..I will work on this...

[#8]

can any body look into the issue... logic is working fine if i look into the log file

but DATA MODEL not showing correct values

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

price:

load * inline [

prod,  start,      end,      price, cate

A,          1 ,        5,          100,q1

A,          6,          10,          200,q2

A,          11,        20,        300,q3

A,          21,        30,        400,q4

];

target:

load * inline

[

prod, q1, q2, q3, q4

A, 6, 8, 5, 9];

target1:

CrossTable(cate,Data)

load prod, q1,q2,q3,q4 Resident target;

t3:

load

    rowno() as sr,

    Data,

    Previous(Data) as d,

    cate

resident target1;

drop table target;

drop table target1;

t4:

load

    sr,

    Data,

    d,

    cate,

    If(rowno()=1, Data, peek([min], -1)+ Data) as [min],

    If(rowno()=1, Data, peek([Pcount], -1)+ Data) as [Pcount]

Resident t3;

drop table t3;

t5:

load   

    sr,

    cate,

    if(sr=1 ,1, previous(min+1)) as min,

    Pcount

Resident t4;

let vRows = peek('sr',-1,'t5');

drop table t4;

// Example 1

sub price_sub (I,X)

pqr_r:

load

//    cate,

    concat(if($(I)>=1 and $(I)<=5, [price],(if($(I)>=6 and $(I)<=10 ,[price],(if($(I)>=11 and $(I)<=20,[price],(if($(I)>=21 and $(I)<=30,price,0))))))),$(X)) as pqr

    Resident price

    where start <= $(I) and end >=$(I);

end sub;

//join

//load * Resident cate;

for i = 0 to  $(vRows)-1

    let vmin = peek('min',$(i),'t5');

    let vPcoutn = peek('Pcount',$(i),'t5');

    let vcate= peek('cate',$(i),'t5');

    trace >> $(vmin);

    trace >> $(vPcoutn);

    trace >> $(vcate);

    for r = $(vmin) to $(vPcoutn)

            call price_sub($(r),$(vcate));

    next r;

next i;@

//expected o/p:

//prod      q1    q2          q3        q4

//A          700    2000    1500      3500

[anbu1984 · Accepted solution]

Try this

Target:

load * Inline [

prod,q1,q2,q3,q4

A, 6, 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, price Resident price while IterNo() <= (end -start +1);

Final:

Load prod,sum(If(start<=q1,price)) as sum1,sum(If(start>q1 And start <=q1+q2,price)) as sum2,sum(If(start>q1+q2 And start <=q1+q2+q3,price)) as sum3,

sum(If(start>q1+q2+q3 And start <=q1+q2+q3+q4,price)) as sum4 Resident Target Group by prod;

Drop table price;

[sibideepak]

Thanks anbu cheliyan ,

Working perfect .

Thanks a lot .

Saves my time.