topic Re: How to get previous & next row values through script in QlikView

How to get previous & next row values through script

karthikeyan1504 — Sat, 15 Aug 2015 08:21:29 GMT

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.

Re: How to get previous & next row values through script

MK_QSL — Sat, 15 Aug 2015 08:37:16 GMT

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;

Re: How to get previous & next row values through script

swuehl — Sat, 15 Aug 2015 08:38:39 GMT

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

;

Re: How to get previous & next row values through script

sunny_talwar — Sat, 15 Aug 2015 08:45:16 GMT

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;

Re: How to get previous & next row values through script

sunny_talwar — Sat, 15 Aug 2015 09:09:48 GMT

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

];

Re: How to get previous & next row values through script

sunny_talwar — Sat, 15 Aug 2015 09:14:19 GMT

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.

Re: How to get previous & next row values through script

karthikeyan1504 — Sat, 15 Aug 2015 09:28:33 GMT

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

Thanks for your response..

Re: How to get previous & next row values through script

karthikeyan1504 — Sat, 15 Aug 2015 09:29:40 GMT

Thank you everyone..

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

Warm Regards,

Karthikeyan.

Re: How to get previous & next row values through script

sunny_talwar — Sat, 15 Aug 2015 09:32:16 GMT

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.

Re: How to get previous & next row values through script

MK_QSL — Sat, 15 Aug 2015 09:47:21 GMT

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;

Re: How to get previous & next row values through script

Anonymous — Sat, 15 Aug 2015 09:55:40 GMT

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

Re: How to get previous & next row values through script

MK_QSL — Sat, 15 Aug 2015 10:13:18 GMT

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

Re: How to get previous & next row values through script

Anonymous — Sat, 15 Aug 2015 10:17:16 GMT

Sure thanks Manish.

Regards

Neetha

Re: How to get previous & next row values through script

MK_QSL — Sat, 15 Aug 2015 11:23:06 GMT

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 !

Re: How to get previous & next row values through script

sunny_talwar — Sat, 15 Aug 2015 11:54:39 GMT

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.

Re: How to get previous & next row values through script

swuehl — Sat, 15 Aug 2015 12:27:55 GMT

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.

Re: How to get previous & next row values through script

MK_QSL — Sat, 15 Aug 2015 12:39:06 GMT

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;

Re: How to get previous & next row values through script

swuehl — Sat, 15 Aug 2015 12:50:54 GMT

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).

Re: How to get previous & next row values through script

MK_QSL — Sat, 15 Aug 2015 13:01:41 GMT

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

Re: How to get previous & next row values through script

karthikeyan1504 — Sat, 15 Aug 2015 13:36:51 GMT

Thanks Sunny...

Let me see once I complete the full data model.

Warm Regards,

Karthikeyan.