Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;