create an historical interval with fromDate and Todate

[JMAROUF]

Hi every one,

I want to create a fromDate, toDate fields basing on an historical table, i have 3 date:

- effective date

- event date

- due date

here is my code :

policy_H_tmp:
NoConcatenate
LOAD policy_ID,
     date(EFFECTIVE_DATE,'DD/MM/YYYY') as EFFECTIVE_DATE,
     date(EVENT_DATE,'DD/MM/YYYY') as  EVENT_DATE,
     date(DUE_DATE,'DD/MM/YYYY') as DUE_DATE,
     CODE_INT,
    autonumber(CODE_INT,policy_ID) as rownumber_H
FROM  […….\policy.xls]  (ooxml, embedded labels, table is Sheet1);

LEFT JOIN(policy_H_tmp)

load 
policy_ID,
date(max(DUE_DATE),'DD/MM/YYYY') as MAX_DUE_DATE ,
max(rownumber_H) as rownumber_H
resident policy_H_tmp GROUP BY policy_ID;

policy_H:
NoConcatenate
load Distinct
policy_ID,
CODE_INT,
date(Min(date#(EVENT_DATE,'DD/MM/YYYY'))) as FromDtae,
date(date(MAX(if(isnull(MAX_DUE_DATE),EVENT_DATE,MAX_DUE_DATE +1)))-1)  as ToDate
resident policy_H_tmp  group by policy_ID,CODE_INT ,rownumber_H order by policy_ID;

drop table police_H_tmp;

with this code i found a problem in policy : 656470115000131  it  gives me fromDate -> 06/04/2015 toDate -> 06/04/2016 CODE_INT -> 5700, which is wrong it must show toDate -> 05/04/2017

i have joined my issue in attachment, the file named data represents my historical table while the other file is what i want,   can i do this with qlikview?

thank you for advance.

[JMAROUF · Accepted solution]

this solution works.

policy_H_tmp:
NoConcatenate
LOAD policy_ID,
     date(EFFECTIVE_DATE,'DD/MM/YYYY') as EFFECTIVE_DATE,
     date(EVENT_DATE,'DD/MM/YYYY') as  EVENT_DATE,
     date(DUE_DATE,'DD/MM/YYYY') as DUE_DATE,
     CODE_INT,
    autonumber(CODE_INT,policy_ID) as rownumber_H
FROM  […….\policy.xls]  (ooxml, embedded labels, table is Sheet1);

LEFT JOIN(policy_H_tmp)

load 
policy_ID,
date(max(DUE_DATE),'DD/MM/YYYY') as MAX_DUE_DATE ,
max(rownumber_H) as rownumber_H
resident policy_H_tmp GROUP BY policy_ID;

LEFT JOIN(policy_H_tmp)

load distinct
police,
num(rownumber_H)-1 as rownumber_H,
date(min(EVENT_DATE),'DD/MM/YYYY') as MAX_EVENT_DATE
resident police_H_tmp GROUP BY police,rownumber_H;

policy_H:
NoConcatenate
load Distinct
policy_ID,
CODE_INT,
date(Min(date#(EVENT_DATE,'DD/MM/YYYY'))) as FromDtae,
date(date(MAX(if(isnull(MAX_DUE_DATE),MAX_EVENT_DATE,MAX_DUE_DATE +1)))-1)  as ToDate
resident policy_H_tmp  group by policy_ID,CODE_INT ,rownumber_H order by policy_ID;

drop table police_H_tmp;