topic Next row value in QlikView

Next row value

Fri, 19 Apr 2013 09:51:13 GMT

Hello,

I have a spreadsheet which requires the next row of data so that I can calculate the closing stock of the day before (i.e. users only input the opening stock per day and thus I need to look at the next days opening stock to calculate the closing stock of the previous day)

Date	Opening Stock	Closing Stock
26/03/13	2344	5000
27/03/13	5000	6780
28/03/13	6780	9000
29/03/13	9000	1000

So for the above example for the 27/03/13 I currently only have the opening stock of 5000 inputted and I need to look at the 28/03/13 opening stock in order to complete the closing stock for 27/03/13. Obviously peek could be used if I was looking for the previous value but I'm not sure as to how to look at the next field value.

Thanks,

Ralph

Re: Next row value

alexandros17 — Fri, 19 Apr 2013 09:53:22 GMT

look af "Above" and "Below" functions in the guite they fit ...

Re: Next row value

Fri, 19 Apr 2013 09:54:56 GMT

Above is an option although ideally I would like to calculate this in the script rather than a chart.

Thanks

Re: Next row value

alexandros17 — Fri, 19 Apr 2013 09:57:27 GMT

you can use peek even for new row ..

if you have a loop

for i= 0 to rowno()

let val=peek('field', $(i)+1, 'mytable'

where $(i)+1 is next row

Re: Next row value

Fri, 19 Apr 2013 10:16:50 GMT

Can you provide a simple example for the above as I don't think I've used a loop before.

Thanks

Re: Next row value

kaushiknsolanki — Fri, 19 Apr 2013 10:39:35 GMT

Hi,

Try this script

Data:
Load Date(Date#(Date,'DD/MM/YYYY')) as Date,ClosingStock inline [
Date, ClosingStock
26/03/13,5000
27/03/13,6780
28/03/13,9000
29/03/13,1000
];

Data1:
Load Date as Date1,ClosingStock as ClosingStock1,Previous(ClosingStock) as OpeningStock
Resident Data Order by Date Asc;

Regards,

Kaushik Solanki

Re: Next row value

Fri, 19 Apr 2013 10:56:40 GMT

Hi Kaushik,

I'm trying to generate the next field value and not the previous value, and it would be based on the opening stock value rather than the closing.

Thanks,

Ralph

Re: Next row value

jagan — Fri, 19 Apr 2013 11:00:34 GMT

Hi,

Try Previous() in script to get previous record field value,

LOAD

ClosingStock,

If(RowNo() = 1, OpeningStock, Previous(ClosingStock)) AS OpeningStock

Date

FROM DataSource

ORDER BY Date;

Hope this helps you.

Regards,

Jagan.

Re: Next row value

alexandros17 — Fri, 19 Apr 2013 11:01:11 GMT

...

FOR

i = 0 to NoOfRows('myTable')

LET var= Trim(Peek('myField', $(i), 'myTable'));

.....

NEXT

hope it helps

Re: Next row value

kaushiknsolanki — Fri, 19 Apr 2013 11:03:01 GMT

Hi,

Do you want to generate Opening Stock from Closing Stock

Closing Stock from Opening Stock

Regards,

Kaushik Solanki

Re: Next row value

Fri, 19 Apr 2013 11:08:54 GMT

I want to generate the closing stock from the opening stock, so the closing stock figure will only be generated when the next days opening stock is inputted.

Thanks,

Ralph

Re: Next row value

kaushiknsolanki — Fri, 19 Apr 2013 11:16:36 GMT

Hi,

Try this.

Data:
Load Date(Date#(Date,'DD/MM/YYYY')) as Date,Opening_Stock inline [
Date, Opening_Stock
26/03/13, 2344
27/03/13, 5000
28/03/13, 6780
29/03/13, 9000
];

Data1:
Load Date as Date1,Opening_Stock as Opening_Stock1,Previous(Opening_Stock) as ClosingStock
Resident Data Order by Date Desc;

Here because we are sorting the Date in descending order your previous function will act as next.

Regards,

Kaushik Solanki

Re: Next row value

Fri, 19 Apr 2013 12:18:12 GMT

Many thanks for this, it created the closing stock as expected.

Re: Next row value

sujeetsingh — Fri, 08 Aug 2014 08:39:10 GMT

A nice trick and concept of maths.