i created below script based upon these conditions ..
Conditions:
1) if policy did not complete in one year and came first time in records then this is consider to be as New and year should be current year (from date year and UW Year should be current year)
2) If policy did not complete in one year and same policy available in previous year then consider to be as Renew
3) Policy which is available to previous years but not available in current year then considered to be as LOST
based upon above conditions i created this script
Load Distinct
POLICY_NO as [Child Policy],
OLD_POLICY_NUMBER as [Parent Policy],
SA_YEAR,
Date(POLICY_EFFECTIVE_DATE) as [From Date],
Date(POLICY_EXPIRY_DATE) as [To Date],
Policy_Effective_Year,
Month_Difference,
**If(POLICY_NO = Previous(POLICY_NO),NULL(),
if(Policy_Effective_Year=Year(Today()) and
SA_YEAR=Year(Today()),Month_Difference < 12)) AS New,
If(Len(OLD_POLICY_NUMBER)>0,SA_YEAR<YEAR(TODAY())) AS RENEW**
FROM
[D:\test\table_1.qvd] (qvd);
from above script i created new and renew .. and want to confirm is this right and further more i want to create same for lost..
and i want to create only one column of "Status" where all status will fulfilled
this is the link of data file
https://filebin.net/0f3nx8h2nvpv9qtv
Any help
