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.

[MK_QSL]

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


Left Join (Temp1)
Load
  Year,
  Previous([Exchange Rate]) as NextER
Resident Temp1
Order By Year Desc;


Left Join (Temp1)
Load
  Year,
  Previous([Exchange Rate]) as PreviousER
Resident Temp1
Order By Year Asc;

[swuehl · Accepted solution]

You can do it like this:

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

];

LOAD Year,

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

  [Exchange Rate]*[Sales Amount] as Current

RESIDENT INPUT

;

LEFT JOIN

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

RESIDENT INPUT

ORDER BY Year desc

;

[sunny_talwar]

This?

Script:

Table:

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

];

NewTable:

LOAD Year,

  [Sales Amount],

  [Exchange Rate],

  Alt(Peek('Exchange Rate'), 0) as NextXR

Resident Table

Order By Year;

DROP Table Table;

Table:

LOAD *,

  [Sales Amount] * [Exchange Rate] as CurrSalesAmt,

  [Sales Amount] * NextXR as NextSalesAmt,

  [Sales Amount] * PreviousXR as PreviousSalesAmt;

LOAD Year,

  [Sales Amount],

  [Exchange Rate],

  NextXR,

  Alt(Peek('Exchange Rate'), 0) as PreviousXR

Resident NewTable

Order By Year desc;

DROP Table NewTable;

[sunny_talwar]

Another method using Mapping Load:

Mapping:

Mapping

LOAD * Inline [

Year, Exchange Rate

2006, 1.5

2007, 2

2008, 2.5

2009, 1

2010, 1.5

];

Table:

LOAD Year,

  [Sales Amount] * ApplyMap('Mapping', Year) as CurSalesAmt,

  [Sales Amount] * ApplyMap('Mapping', Year - 1, 0) as NextSalesAmt,

  [Sales Amount] * ApplyMap('Mapping', Year + 1, 0) as PreviousSalesAmt;

LOAD * Inline [

Year, Sales Amount

2006, 100

2007, 200

2008, 300

2009, 200

2010, 100

];

[sunny_talwar]

I was wondering why Peek was not working and forgot to use Previous. But I still don't know why Previous works and Peek doesn't when using Join.

[karthikeyan1504]

Yes.. Even I tried with Peek and Previous but it gave different output..

Thanks for your response..

[karthikeyan1504]

Thank you everyone..

All your responses are overwhelming... My job is getting smarter day by day being in community..

Warm Regards,

Karthikeyan.

[sunny_talwar]

Not a problem.

I am glad we were helpful.

Best,

Sunny

P.S. even though you got your solution, but I would suggest looking into the mapping load option as well.

[MK_QSL]

Temp1:
Load * Inline
[
  Year, Exchange Rate, Sales Amount
  2006, 1, 100
  2007, 2, 200
  2008, 3, 300
  2009, 4, 200
  2010, 5, 100
];

Left Join (Temp1)
Load
  RowNo() as ID,
  Year,
  Peek('Exchange Rate',RowNo(),'Temp1') as NextER
Resident Temp1
Order By Year Asc;


Drop Field ID;


Temp2:
Load
  Year, [Exchange Rate] as TempRate
Resident Temp1
Order By Year Desc;


Left Join (Temp1)
Load
  RowNo() as ID,
  Year,
  TempRate,
  Peek('TempRate',RowNo(),'Temp2') as PreER
Resident Temp2
Order By Year Desc;


Drop Table Temp2;
Drop Field ID;