UPDATE

[upaliwije]

Hi,

I have created a QV document where I have Following fields

Policy_no, Period_From, Period_to

The policy Number has several records with different periods. I want to find out whether   a particular policy has been renewed in the following month where period should be the immediate next period of the current period.

If policy is renewed the it Should be marked with 'Y'   in a new field 'REN' otherwise 'N'

Could you  please help me in this development in my QV document attached here to

[settu_periasamy]

Hi,

May be like this..

T1:
LOAD POLICY_NO,
     Date(PERIOD_FROM) as PERIOD_FROM,
      Date(PERIOD_TO) as PERIOD_TO
FROM
test.xls
(biff, embedded labels);
NoConcatenate
T2:
LOAD POLICY_NO,
      PERIOD_FROM,
      PERIOD_TO,
      if(POLICY_NO=PREVIOUS(POLICY_NO),
      iF(PERIOD_FROM-Previous(PERIOD_TO)=1,'Y','N')) as ren
Resident T1 Order by POLICY_NO,PERIOD_FROM;

DROP Table T1;

[jonathandienst · Accepted solution]

This will do it:

Data:

LOAD *,

  Year(PERIOD_FROM)*12 + Month(PERIOD_FROM) As SeqFrom,

  Year(PERIOD_TO)*12 + Month(PERIOD_TO) As SeqTo

;

LOAD POLICY_NO,

     PERIOD_FROM,

     PERIOD_TO

FROM test.xls

(biff, embedded labels);

T1:

LOAD *,

  If(POLICY_NO = Previous(POLICY_NO) And SeqFrom <= Previous(SeqTo)+1, 'Y', 'N') As REN

Resident Data

Order By POLICY_NO, PERIOD_TO;

DROP Table Data;

[PradeepReddy]

Try like this....

Temp:
Load Policyno,
  Period_From,
  Period_To
From Source;


Final:
Load *,
  if(peek(Policyno)=Policyno and addmonths(peek(Period_From),-12)=Period_From,'Y','N') as Ren
resident Temp order by Policyno asc,Period_From desc;


Drop table Temp;