Peek

[upaliwije]

Hi Friends

I have following data table

Policy_No, Period

1                1

2                 1

3                 2

4                 1

1                 2

2                 3

1                 3

I want the following output table

Policy_No, Period  Last_Period

1                1          1

1                2           1

1                 3          2

2                 1          1

2                  3          1

3                 2          2

4                 1          1

I have written following script

sales:

Load

Policy_No,

Period

from sales.qvd

(qvd);

sales1;

load

Policy_No,

Period,

if(rowno()=1,PERIOD,peek(POLICY_NO,-1),PERIOD)as LAST_PERIOD

resident sales   ORDER BY POLICY_NO,PERIOD asc;

But I do not get desired result for Last period

Pls look into my script and correct it If I m wrong

[Gysbert_Wassenaar]

Try:

LOAD POLICY_NO, PERIOD, if(previous(POLICY_NO)=POLICY_NO,peek(PERIOD)) as LAST_PERIOD

Resident sales

ORDER BY POLICY_NO, PERIOD

;

[sunny_talwar · Accepted solution]

Try this:

Sales:

LOAD * INLINE [

    Policy_No, Period

    1, 1

    2, 1

    3, 2

    4, 1

    1, 2

    2, 3

    1, 3

];

NewSales:

LOAD Policy_No,

  Period,

  If(Peek('Policy_No') = Policy_No, Peek(Period), Period) as Last_Period

Resident Sales

ORDER BY Policy_No, Period;

DROP Table Sales;

Best,

Sunny

[upaliwije]

Thanks

It is working but last_Period for 1st record remains blank. Pls help

[Gysbert_Wassenaar]

And which value do you expect? What is the previous period of the first period?

[upaliwije]

If it is first record Last_period value should be the same value shown in period

Eg

Policy_No, Period  Last_Period

1                1          1

[sunny_talwar]

Did you try the script I provided? It seems to work, give it a shot

Best,

Sunny

[Peter_Cammaert]

Then in Gysbert's code, replace

...peek(PERIOD)...

with

...peek(PERIOD), PERIOD...

This will set Last_Period to the current value of PERIOD in all cases where POLICY_NO <> previous(POLICY_NO). That includes the very first record of the table.

[upaliwije]

Thank u

[sunny_talwar]

Not a problem, I am glad we were able to help.

Best,

Sunny