Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Next row value

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)

DateOpening StockClosing Stock
26/03/1323445000
27/03/1350006780
28/03/1367809000
29/03/1390001000

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

1 Solution

Accepted Solutions

Re: Next row value

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

13 Replies

Re: Next row value

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

Not applicable

Re: Next row value

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

Thanks

Re: Next row value

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'

next

where $(i)+1 is next row

Not applicable

Re: Next row value

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

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

Not applicable

Re: Next row value

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

MVP & Luminary
MVP & Luminary

Re: Next row value

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

...

FOR

i = 0 to NoOfRows('myTable')

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

.....

NEXT

hope it helps

Re: Next row value

Hi,

    Do you want to generate Opening Stock from Closing Stock

     or

     Closing Stock from Opening Stock

Regards,

Kaushik Solanki