topic Re: Peek in QlikView

Peek

upaliwije — Tue, 21 Apr 2015 08:57:23 GMT

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

Re: Peek

Gysbert_Wassenaar — Tue, 21 Apr 2015 09:19:35 GMT

Try:

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

Resident sales

ORDER BY POLICY_NO, PERIOD

;

Re: Peek

sunny_talwar — Tue, 21 Apr 2015 10:24:31 GMT

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

Re: Peek

upaliwije — Tue, 21 Apr 2015 11:04:16 GMT

Thanks

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

Re: Peek

Gysbert_Wassenaar — Tue, 21 Apr 2015 11:07:13 GMT

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

Re: Peek

upaliwije — Tue, 21 Apr 2015 11:10:57 GMT

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

Policy_No, Period Last_Period

1 1 1

Re: Peek

sunny_talwar — Tue, 21 Apr 2015 11:15:30 GMT

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

Best,

Sunny

Re: Peek

Peter_Cammaert — Tue, 21 Apr 2015 11:22:54 GMT

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.

Re: Peek

upaliwije — Tue, 21 Apr 2015 12:26:04 GMT

Thank u

Re: Peek

sunny_talwar — Tue, 21 Apr 2015 12:26:52 GMT

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

Best,

Sunny