topic Re: Generate stock for every day in QlikView

Generate stock for every day

Anonymous — Mon, 29 Oct 2012 21:50:57 GMT

There must be someone who has done this before.

I need to create stock information for ever date, in the script, but I only got the begin amount and the changes.

I think it is easiest to explane with an example.
Here is an example of what I have and want:

I have the changes

Product	30-10-2012	31-10-2012	1-11-2012	2-11-2012	3-11-2012	4-11-2012	5-11-2012	6-11-2012
AAA	20	1	-1		33		5	6
AAB
AAC	100	3
BBB	1	3	-10	5	0	22	0	45
BBC	23	3			-2		-6
CCC		3	4			-5		12

This is the result I am looking for:

Product	30-10-2012	31-10-2012	1-11-2012	2-11-2012	3-11-2012	4-11-2012	5-11-2012	6-11-2012
AAA	20	21	20	20	55	55	60	66
AAB	0	0	0	0	0	0	0	0
AAC	100	103	103	103	103	103	103	103
BBB	1	4	-6	1	1	23	23	48
BBC	23	26	26	26	24	24	18	18
CCC	0	3	7	7	7	2	2	14

So for every day I want to have the amount that is in stock based on the changes.

What is the way to do this in the script?

Thanks,

Dennis.

Re: Generate stock for every day

thomas_skariah — Tue, 30 Oct 2012 03:12:18 GMT

Hi Dennis,

You can try this code in script

If(Date > Previous(Date), RangeSum(Data, Peek('DateNew')), Data) AS DateNew

Regards,

Tom

Re: Generate stock for every day

Anonymous — Tue, 30 Oct 2012 10:45:15 GMT

Thanks Tom , but that doesn't get any results....

Re: Generate stock for every day

Anonymous — Tue, 30 Oct 2012 10:50:16 GMT

I am a bit closer now I think because I have sorted the data in the right way now.

What I got now is data like this:

Product ID	date	Amount
AA	30-10-2012	4
AA	31-10-2012
AA	1-11-2012
AA	2-11-2012	6
AA	3-11-2012
AA	4-11-2012	-3
AA	5-11-2012
AA	6-11-2012
BB	30-10-2012	1
BB	31-10-2012
BB	1-11-2012	100
BB	2-11-2012
BB	3-11-2012
BB	4-11-2012	-25
BB	5-11-2012
BB	6-11-2012	3

What I want is:

Product ID	date	Amount
AA	30-10-2012	4
AA	31-10-2012	4
AA	1-11-2012	4
AA	2-11-2012	10
AA	3-11-2012	10
AA	4-11-2012	7
AA	5-11-2012	7
AA	6-11-2012	7
BB	30-10-2012	1
BB	31-10-2012	1
BB	1-11-2012	101
BB	2-11-2012	101
BB	3-11-2012	101
BB	4-11-2012	76
BB	5-11-2012	76
BB	6-11-2012	79

Any suggestions?

Thanks,

Dennis.

Re: Generate stock for every day

Gysbert_Wassenaar — Tue, 30 Oct 2012 11:15:07 GMT

You can use peek to calculate the Amount field

LOAD [Product ID],date,

if(rowno()=1,

Amount,

if([Product ID] = peek([Product ID]),

if(len(Amount)=0,

peek(Amount),

peek(Amount)+Amount),

Amount)

) as Amount

INLINE [

Product ID, date, Amount

AA, 30-10-2012, 4

AA, 31-10-2012,

AA, 01-11-2012,

AA, 02-11-2012, 6

AA, 03-11-2012,

AA, 04-11-2012, -3

AA, 05-11-2012,

AA, 06-11-2012,

BB, 30-10-2012, 1

BB, 31-10-2012,

BB, 01-11-2012, 100

BB, 02-11-2012,

BB, 03-11-2012,

BB, 04-11-2012, -25

BB, 05-11-2012,

BB, 06-11-2012, 3

];

Re: Generate stock for every day

Anonymous — Tue, 30 Oct 2012 11:16:20 GMT

I think I got it with Toms solution:

IF(ProductID =Previous(ProductID) , RangeSum(Amount, Peek('Stock') ) , Amount) as Stock

What do you think?

Re: Generate stock for every day

Anonymous — Tue, 30 Oct 2012 11:19:22 GMT

I am goin to try this too.

Thanks Gysbert.