How to get previous & next row values through script

[karthikeyan1504]

Hi,

I have requirement from client as briefed below,

The current year sales amount should be multiplied by current, previous & next year exchange rate.

Please refer the data below,

Year, Exchange Rate, Sales Amount

2006, 1.5, 100

2007, 2, 200

2008, 2.5, 300

2009, 1, 200

2010, 1.5 , 100

So, from above data set I need to get the output as mentioned below,

Previous Year	Current Year	Next Year
2006	100*1.5 = 150	100*2= 200
2007	200*1.5= 300	200*2= 400	200*2.5= 500
2008	300*2= 600	300*2.5=750	300*1=300
2009	200*2.5=500	200*1=200	200*1.5=300
2010	100*1=100	100*1.5=150

Could anyone please help to achieve this scenario via script?

Thanks & Regards,

Karthikeyan.

[Anonymous]

Hi Manish,

Please can you explain difference between two solutions given by you.

in first solution Previous Function was used and second Peek Function.

Thanks & Regards

Neetha

[MK_QSL]

Hi Neetha,

Request you to go through about the Previous and Peek functions and try to use my code step by step.

You will realize the difference..

Thanks

[Anonymous]

Sure thanks Manish.

Regards

Neetha

[MK_QSL]

hic‌ . Could you please explain us why Previous is working with Asc and Desc order but Peek is not working ?

I have done some work around in script for Peek function but not getting the logic behind Peek function now giving the same result as Previous.

Thaanks !

[sunny_talwar]

While we are inviting Henric for explanation, why not get some information on RowNo() and RecNo() as well. I have seen it behaving similar to Peek and Previous, respectively.

[swuehl]

I do get the same results using Peek():

SET ThousandSep=',';

SET DecimalSep='.';

INPUT:

LOAD * INLINE [

Year, Exchange Rate, Sales Amount

2006, 1.5, 100

2007, 2, 200

2008, 2.5, 300

2009, 1, 200

2010, 1.5 , 100

];

Years:

LOAD Year,

  [Exchange Rate],

  Peek([Exchange Rate])*[Sales Amount] as Previous,

  [Exchange Rate]*[Sales Amount] as Current

RESIDENT INPUT

;

LEFT JOIN

LOAD Year, [Exchange Rate], Peek([Exchange Rate])*[Sales Amount] as Next

RESIDENT INPUT

ORDER BY Year desc

;

DROP FIELD [Exchange Rate] FROM Years;

Regarding peek() vs previous() and rowno() vs recno(), it's all about input vs. output table records.

From Counters in the Load

Using RecNo()

The RecNo() function simply counts the input records and returns the number of the current record. Simple, and very useful if you want to create a record ID. However, if you concatenate several input files, or use a While clause, the numbers will not be unique.

Using RowNo()

The RowNo() function is very similar to the RecNo(), but this instead counts the output records and returns the number of the current record. Also simple and useful, especially if you concatenate several input tables. In such a case, the function will return consecutive numbers for the output table.

[MK_QSL]

Hi Stefen,

Let me know why we are not getting results below?

What is being missed? I have already spent 2 hours to understand the behavior but failed.

I have already tried

Peek([Exchange Rate])

Peek('Exchange Rate')

Peek('[Exchange Rate]')

Objective is simply to get Previous and Next Exchange Rate for each Year.
I am sure, something I missed which is not allowing me to get the desired result.

INPUT:

LOAD * INLINE

[

  Year, Exchange Rate, Sales Amount

  2006, 1, 100

  2007, 2, 200

  2008, 3, 300

  2009, 4, 200

  2010, 5, 100

];

Left Join (INPUT)

LOAD

  Year,

  Previous([Exchange Rate]) as Previous1,

  Peek('Exchange Rate') as Previous2

RESIDENT INPUT;

LEFT JOIN (INPUT)

LOAD

  Year,

  Previous([Exchange Rate]) as Next1,

  Peek('Exchange Rate') as Next

RESIDENT INPUT

ORDER BY Year Desc;

[swuehl]

INPUT:

LOAD * INLINE

[

  Year, Exchange Rate, Sales Amount

  2006, 1, 100

  2007, 2, 200

  2008, 3, 300

  2009, 4, 200

  2010, 5, 100

];

Left Join (INPUT)

LOAD

  Year,

  [Exchange Rate],

  Previous([Exchange Rate]) as Previous1,

Peek('Exchange Rate') as Previous2

RESIDENT INPUT;

LEFT JOIN (INPUT)

LOAD

  Year,

[Exchange Rate],

  Previous([Exchange Rate]) as Next1,

Peek('Exchange Rate') as Next

RESIDENT INPUT

ORDER BY Year Desc;

You need to take care that [Exchange Rate] is part of your output table when you want to use Peek() function.

I believe the relevant output table here is looking at the table before the JOIN happens.

Unfortunately, Peek() just silently fails when the field does not exist (well, wait, probably that's by design, otherwise you would probably run into trroubles when you want to peek() fields that are just created in the same LOAD).

[MK_QSL]

Ohhh! Clear now.. I was using Peek function without loading [Exchange Rate].

[karthikeyan1504]

Thanks Sunny...

Let me see once I complete the full data model.

Warm Regards,

Karthikeyan.