How to seperate two fields from the combination of... - Qlik Community

nevilledhamsiri · ‎2017-10-14

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

nevilledhamsiri · ‎2017-10-15

Expect some one to help me out n this!

Regds

Neville

jonathandienst · ‎2017-10-17

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

techvarun · ‎2017-10-17

Check the attacked.

Is this what you want?

vinayakg · ‎2017-10-17

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;

nevilledhamsiri · ‎2017-10-17

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

vinayakg · ‎2017-10-18

nevilledhamsiri‌

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.

nevilledhamsiri · ‎2017-10-18

Dear Vinayak,

Please do attach your working which is not there.

nevilledhamsiri · ‎2017-10-19

Since I am using a personnel edition, cannot view your doc. Please send me the screen

Regds

Neville

vinayakg · ‎2017-10-20

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

How to seperate two fields from the combination of two dimnsions!