How to seperate two fields from the combination of... - Page 2 - 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-21

Dear Vinayak! & all our experts;

This is how I loaded data but error comes as given below!

Also my requirement is as follows:

To arrange a separate field for new & renewal

All policies which carry 2017 (Year which carries 17) need to be considered as New

2015 & below needs to be Renewals

The issue is only 2016. To break 2016 policies in to New & Renewals becomes little challenging! That should be as follows:

If the Credit note date January 2017, all 2016 policies needs to be considered as New

If the credit note date is February 2017 & the policies of 2016 with their period from date falls in to January requires to be treated as Renewal (Cancelled a policy in February 2017 due to 2016 January policy)

If the credit note date is March 2017 & the policies of 2016 with their period from date falls in to January & February requires to be treated as Renewals ( Cancelled a policy in March 2017 due to 2016 Jan & Feb policies) & this pattern needs to be continued to be up to December & in December again policies cancelled in December 2017 of policies due to 2016 Nov & below requires to be renewals) If I am to sum up this it should look like below

A: All policies due to 2017 (NEW)

B :All policies due to 2015 & below (2015,2014,2013,2012 etc) (RENEWALS)

C :Always refunded policies in 2017 due to 2016, (To be arranged as per above pattern see it again below)

Credit note date Period from Date

2017_Jan No Renewals (2016) but all 2016 policies should be New

2017-Feb Renewals (2016 Jan policies) others(Feb_Dec) in 2016 New

2017-Mar Renewals (2016 Jan+Feb) others(Mar_Dec) in 2016 New

2017-Apr Renewals (2016 Jan+Feb+Mar) others (Apr_Dec) in 2016 New

2017_May Renewals (2016 Jan+Feb+Mar+Apr) others (May_Dec) in 2016 New

Like wise this combination will continue & in December again Dec _Dec in 2016 only will remain new.

Please refer this pattern & combination & suggest an if statement in the script to create a field for New & Renewals. If this expression can be made as an expression no harm but wish to have a separate field for new & Renewals

Thanks In Advance

Neville

REFUNDS:

LOAD BRANCH, CLASS_CODE, PRODUCT_CODE, POLICY_NO, NAME_OF_INSURED, CREDIT_NOTE_NO,

CREDIT_NOTE_DATE, PERIOD_FROM, PERIOD_TO, PREMIUM, ADDRES, REASON, FINANCIAL_INTEREST,

ME_CODE, ME_NAME,

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 REFUNDS

FROM (biff, embedded labels, table is Sheet1$);

Table not found error

Table 'REFUNDS' not found

REFUNDS:

LOAD BRANCH, CLASS_CODE, PRODUCT_CODE, POLICY_NO, NAME_OF_INSURED, CREDIT_NOTE_NO,

CREDIT_NOTE_DATE, PERIOD_FROM, PERIOD_TO, PREMIUM, ADDRES, REASON, FINANCIAL_INTEREST,

ME_CODE, ME_NAME,

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 REFUNDS

FROM (biff, embedded labels, table is Sheet1$)

vinayakg · ‎2017-10-24

nevilledhamsiri Try this one,

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#(CREDIT_NOTE_DATE,'DD-MMM-YY'),'DD-MM-YYYY'))))<=(num(month(date(date#(PERIOD_FROM,'DD-MMM-YY'),'DD-MM-YYYY'))))) and (num(year(date(date#(CREDIT_NOTE_DATE,'DD-MMM-YY'),'YYYY'))))<=(num(year(date(date#(PERIOD_FROM,'DD-MMM-YY'),'YYYY'))))-1 ,'Renewed','New') as Status

Resident Policy;

nevilledhamsiri · ‎2017-10-27

Dear Vinayak,

Could you please send me your working & resulted pivot table. I cannot read out your qvw data since I am having A Personnel edition.

Regards

Neville

vinayakg · ‎2017-10-30

Hi nevilledhamsiri‌

Sorry for the delay,

i have attached screenshots of code and resulting table, Please do find the attachments.

Thanks and regards,

Vinayak

nevilledhamsiri · ‎2017-10-30

Dear Vinayak,

The expected out put has not come. All policies below 2016 needs to be Renewals. 2017 policies needs to be New. 2016 policies are broken in to both New & Renewals. Please see my data summery what I have presented. Please modify your formula to acomodate that.

Thanks Neville

vinayakg · ‎2017-10-31

I am Not getting scenario, Can u please explain me what all we need to consider for comparison and which all years and months should show the new and renewed.

if with one small example is appreciated,

regards,

Vinayak

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