topic Re: UPDATE in QlikView

UPDATE

upaliwije — Fri, 19 Feb 2016 04:59:42 GMT

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

Re: UPDATE

settu_periasamy — Fri, 19 Feb 2016 05:14:56 GMT

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;

Re: UPDATE

jonathandienst — Fri, 19 Feb 2016 05:20:00 GMT

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;

Re: UPDATE

PradeepReddy — Fri, 19 Feb 2016 05:53:06 GMT

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;