topic Re: Need help plzz .. in QlikView

Need help plzz ..

sibideepak — Thu, 11 Sep 2014 06:29:00 GMT

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

Re: Need help plzz ..

Thu, 11 Sep 2014 07:20:30 GMT

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

Re: Need help plzz ..

sibideepak — Thu, 11 Sep 2014 07:36:49 GMT

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

Re: Need help plzz ..

MK_QSL — Thu, 11 Sep 2014 08:03:52 GMT

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

Re: Need help plzz ..

Thu, 11 Sep 2014 08:05:17 GMT

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

Re: Need help plzz ..

sibideepak — Thu, 11 Sep 2014 09:11:53 GMT

Hello MRKachhiaIMP ,

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

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

Thanks,

Deepak

Re: Need help plzz ..

MK_QSL — Thu, 11 Sep 2014 09:16:41 GMT

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

Re: Need help plzz ..

Thu, 11 Sep 2014 12:42:43 GMT

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,

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

Re: Need help plzz ..

anbu1984 — Thu, 11 Sep 2014 13:34:05 GMT

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;

Re: Need help plzz ..

sibideepak — Fri, 12 Sep 2014 05:13:17 GMT

Thanks anbu cheliyan ,

Working perfect .

Thanks a lot .

Saves my time.

Re: Need help plzz ..

sibideepak — Fri, 12 Sep 2014 07:27:09 GMT

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

Re: Need help plzz ..

anbu1984 — Fri, 12 Sep 2014 08:53:45 GMT

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];

Re: Need help plzz ..

sibideepak — Fri, 12 Sep 2014 09:24:21 GMT

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

Re: Need help plzz ..

anbu1984 — Fri, 12 Sep 2014 12:37:50 GMT

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;