Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to make a list box for new & renewals from the data given below. Once I loaded this sample data with reference to policy_ no & period from date , i need to separate what is new & renewals. The year from policy_no & the month from period from date to be considered in separating what policies are NEW & what are RENEWALS. (17,14,15,16 are the years as per the policy number but some policy numbers of which length equals to 12, the Year of which is the 1st two letters eg, 04RT1B001008, carries Year as 2004. Once the Year is extracted, 2017 refers to new, all years below 2017 refers to as Renewals. But 2016 yer policies to be matched with period from date. Since this list carries the policies refunded in July, If the Year is 2016, & period from date equal to July & below need to consider as renewals. Eg if the Year of the policy is 2016 & period from date falls in to July & below they are renewals & year is 2016 & from date is above July (Aug to Dec) to be considered as New.
Expect our expert to suggest me an answer for this. If this can be done in the script it is fine because I can make two different fields such as new & renewals.
Thanks
Neville
BRANCH | CLASS_CODE | PRODUCT_CODE | POLICY_NO | NAME_OF_INSURED | CREDIT_NOTE_NO | CREDIT_NOTE_DATE | PERIOD_FROM | PERIOD_TO | PREMIUM |
RA00 | MC | 1D | RA00171D0004042 | MRS. P.G.R.S. KUMARI | RA0017C0009257 | 13-Jul-17 | 1-Feb-17 | 31-Jan-18 | -21339.5 |
RA00 | MC | 1A | RA00171A0002832 | MR. M.A. PIYARATHNA | RA0017C0009215 | 3-Jul-17 | 4-May-17 | 3-May-18 | -73828.13 |
RA00 | M4 | 4F | RA00144F0000101 | MR. H.T. PRIYANTHA | RA0017C0009244 | 10-Jul-17 | 26-Mar-17 | 25-Mar-18 | -6351.05 |
RA00 | MC | 1X | RA00141X0000973 | Mrs U.W. NANDAWATHI | RA0017C0009237 | 10-Jul-17 | 9-May-17 | 8-May-18 | -10207.5 |
RA00 | MC | 1C | RA00151C0002988 | MR. D.J. NIRANJAN. | RA0017C0009274 | 18-Jul-17 | 11-Feb-17 | 10-Feb-18 | -24039.11 |
RA00 | MC | 1D | RA00161D0003943 | MRS K.H.S. JAYAWARDHANA | RA0017C0009255 | 13-Jul-17 | 30-Mar-17 | 29-Mar-18 | -23211.5 |
RA00 | MC | 1A | RA00161A0002549 | MR. A.D. MADUSHANKA | RA0017C0009286 | 21-Jul-17 | 18-Jan-17 | 17-Jan-18 | -91821.07 |
RA00 | MC | 1B | 04RT1B001008 | MR. M.K. SIRIWARDNA | RA0017C0009242 | 10-Jul-17 | 26-Mar-17 | 25-Mar-18 | -34032.44 |
RA00 | FR | DF | RA0015DF0001542 | MR. M.A. NIMAL | RA0017C0009250 | 12-Jul-17 | 13-May-17 | 13-May-18 | -4497.98 |
RA00 | MC | 1A | RA00141A0002115 | MR. W.D. WEERAKOON | RA0017C0009254 | 12-Jul-17 | 3-Jul-17 | 2-Jul-18 | -5062.5 |
RA00 | MC | 1A | RA00161A0002593 | Mr B.A ANURA WICKRAMASINGHE | RA0017C0009247 | 11-Jul-17 | 28-Mar-17 | 27-Mar-18 | -34842.22 |
Expect some one to help me out n this!
Regds
Neville
I think you need to break this down a little. First you need to get the logic to work out the year from the policy. In a new post, write a simple question explaining how to get the year number and I am sure someone will help you. Then you can approach the next issue.
Oh, and please go easy on the decoration - plain text is much easier and more pleasant to read, and all that clutter just confuses.
Check the attacked.
Is this what you want?
try with this in script,
if((num(month(date(date#(PERIOD_FROM,'DD-MMM-YY'),'DD-MM-YYYY'))))>6 and (num(year(date(date#(PERIOD_FROM,'DD-MMM-YY'),'YYYY'))))=2017 ,'New','Renewed') as Date_From
like
Policy:
LOAD * INLINE [
BRANCH, CLASS_CODE, PRODUCT_CODE, POLICY_NO, NAME_OF_INSURED, CREDIT_NOTE_NO, CREDIT_NOTE_DATE, PERIOD_FROM, PERIOD_TO, PREMIUM
RA00, MC, 1D, RA00171D0004042, MRS. P.G.R.S. KUMARI, RA0017C0009257, 13-Jul-17, 1-Feb-17, 31-Jan-18, -21339.5
RA00, MC, 1A, RA00171A0002832, MR. M.A. PIYARATHNA, RA0017C0009215, 3-Jul-17, 4-May-17, 3-May-18, -73828.13
RA00, M4, 4F, RA00144F0000101, MR. H.T. PRIYANTHA, RA0017C0009244, 10-Jul-17, 26-Mar-17, 25-Mar-18, -6351.05
RA00, MC, 1X, RA00141X0000973, Mrs U.W. NANDAWATHI, RA0017C0009237, 10-Jul-17, 9-May-17, 8-May-18, -10207.5
RA00, MC, 1C, RA00151C0002988, MR. D.J. NIRANJAN., RA0017C0009274, 18-Jul-17, 11-Feb-17, 10-Feb-18, -24039.11
RA00, MC, 1D, RA00161D0003943, MRS K.H.S. JAYAWARDHANA, RA0017C0009255, 13-Jul-17, 30-Mar-17, 29-Mar-18, -23211.5
RA00, MC, 1A, RA00161A0002549, MR. A.D. MADUSHANKA, RA0017C0009286, 21-Jul-17, 18-Jan-17, 17-Jan-18, -91821.07
RA00, MC, 1B, 04RT1B001008, MR. M.K. SIRIWARDNA, RA0017C0009242, 10-Jul-17, 26-Mar-17, 25-Mar-18, -34032.44
RA00, FR, DF, RA0015DF0001542, MR. M.A. NIMAL, RA0017C0009250, 12-Jul-17, 13-May-17, 13-May-18, -4497.98
RA00, MC, 1A, RA00141A0002115, MR. W.D. WEERAKOON, RA0017C0009254, 12-Jul-17, 13-Jul-17, 2-Jul-18, -5062.5
RA00, MC, 1A, RA00161A0002593, Mr B.A ANURA WICKRAMASINGHE, RA0017C0009247, 11-Jul-17, 28-Mar-17, 27-Mar-18, -34842.22
];
New:
Load
BRANCH, CLASS_CODE, PRODUCT_CODE, POLICY_NO, NAME_OF_INSURED, CREDIT_NOTE_NO, CREDIT_NOTE_DATE, PERIOD_FROM, PERIOD_TO, PREMIUM,
if((num(month(date(date#(PERIOD_FROM,'DD-MMM-YY'),'DD-MM-YYYY'))))>6 and (num(year(date(date#(PERIOD_FROM,'DD-MMM-YY'),'YYYY'))))=2017 ,'New','Renewed') as Date_From
Resident Policy;
Dear Thanks for your response!, Could you please show me the steps to be followed in the script like what you laid down seems to be not working once I applied. Please take couple of minute to elaborate on this steps by steps
Thanks in advance
Neville
Please Find the attached File,
I Just Loaded the Data First and then in the second Load function added a IF Condition for the Status by accessing the Data through Resident Function.
Dear Vinayak,
Please do attach your working which is not there.
Since I am using a personnel edition, cannot view your doc. Please send me the screen
Regds
Neville
Hey sir,
Sry for late reply,
Just load your data first and then add the expression,
New:
Load
BRANCH, CLASS_CODE, PRODUCT_CODE, POLICY_NO, NAME_OF_INSURED, CREDIT_NOTE_NO, CREDIT_NOTE_DATE, PERIOD_FROM, PERIOD_TO, PREMIUM,
if((num(month(date(date#(PERIOD_FROM,'DD-MMM-YY'),'DD-MM-YYYY'))))>6 and (num(year(date(date#(PERIOD_FROM,'DD-MMM-YY'),'YYYY'))))=2017 ,'New','Renewed') as Date_From
Resident Policy;
Here Policy is the name given to the data u have loaded previously