Solved: Re: Extraction of codes & names seperately from a ... - Qlik Community

nevilledhamsiri · ‎2017-08-31

Hi

I need to extract code as the debtor code & the name as the debtor name from the field debtor.The excel too attached & the length of the debtor is varying from one debtor to the other.please explain with clear steps where & how I get this done?

Neville.

Very sorry our exerts, I have still failed to grasp what you explained. If I am to tell you again what I want is to create two list boxes for Debtor names & debtor codes for that name & codes to be separated. When I followed your steps I get codes & names together as debtor name. Please see below how the excel data once I loaded looks like. Please do write your expression in this data loading so that I will be able to understand better. Sorry for troubling you!

Thanks Neville

Directory;

LOAD DEBTOR,

RISK_DATE,

AS_AT_DATE,

POL_NUMBER,

REF_NUMBER,

INSURED,

AMOUNT,

H

FROM

[..\DEBTORS.xlsx]

(ooxml, embedded labels, table is [DEB(RA)]);

DEBTOR	RISK_DATE	AS_AT_DATE	POL_NUMBER	REF_NUMBER	INSURED	AMOUNT
Debtor Code : 07383 - S.GURUSINGHE	11-Jun-15	31-Aug-17	RA0015CT0000072	RA0015D0047778	B.A.M.H. KURUPPU	0
Debtor Code : 07383 - S.GURUSINGHE	25-Feb-16	31-Aug-17	RA0016CT0000086	RA0016D0051868	B.A.M.H. KURUPPU	0
Debtor Code : 07383 - S.GURUSINGHE	24-May-16	31-Aug-17	RA0016CT0000091	RA0016D0053298	B.A.M.H. KURUPPU	0
Debtor Code : 07383 - S.GURUSINGHE	24-May-16	31-Aug-17	RA0016CT0000092	RA0016D0053299	W.M.G.U. WIJESUNDARA	0
Debtor Code : 07383 - S.GURUSINGHE	20-Dec-16	31-Aug-17	RA0016CT0000104	RA0016D0056906	B.A.M.H. KURUPPU	0
Debtor Code : 07383 - S.GURUSINGHE	2-Jun-17	31-Aug-17	RA00151C0003035	RA0017D0059345	K.G.S. RATHNESEKARA	208,895
Debtor Code : 07383 - S.GURUSINGHE	12-Jun-17	31-Aug-17	RA00171C0003282	RA0017D0059466	. WINWOOD TEA ESTATE (PVT) LTD	1,766
Debtor Code : 07383 - S.GURUSINGHE	26-Jun-17	31-Aug-17	RA00121C0002600	RA0017D0059674	P.K.I.U KUMARASIRI	79,710
Debtor Code : 07383 - S.GURUSINGHE	27-Jun-17	31-Aug-17	RA00121C0002651	RA0017D0059684	P.K.M.L. PINWATTA,	13,057
Debtor Code : 07383 - S.GURUSINGHE	29-Jun-17	31-Aug-17	RA00171C0003288	RA0017D0059707	G.D.D. SENANI	50,322
Debtor Code : 07383 - S.GURUSINGHE	29-Jun-17	31-Aug-17	RA00061A000851	RA0017D0059720	M/S.UNI-TEC LANKA ENGINEERING CO.	52,721
Debtor Code : 07383 - S.GURUSINGHE	30-Jun-17	31-Aug-17	RA00153F0016500	RA0017D0059731	P.K.P. BANDARA	800
Debtor Code : 07383 - S.GURUSINGHE	1-Jul-17	31-Aug-17	RA00081A001253	RA0017D0059747	R.M.S.L RATNAYAKE	58,797
Debtor Code : 07383 - S.GURUSINGHE	1-Jul-17	31-Aug-17	RA00121C0002610	RA0017D0059749	M.D.D.A.K. PEIRIS	22,607
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00151C0003054	RA0017D0059762	R.U. JAYASEKARA.	41,502
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00151C0003060	RA0017D0059763	U.N. MARKETING (PVT) LTD	33,759
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00151C0003056	RA0017D0059764	R.U. JAYASEKARA.	22,713
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00111F000952	RA0017D0059765	B.M.H. KURUPPU	9,565
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00121C0002621	RA0017D0059766	L.H.D. SHANTHALAL	53,244
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00151C0003066	RA0017D0059769	. WINWOOD TEA ESTATE (PVT) LTD	68,992
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00151C0003065	RA0017D0059771	. WINWOOD TEA ESTATE (PVT) LTD	68,992
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00151C0003059	RA0017D0059772	. HENATHENNA TEA FACTORY (PVT) LT	35,196
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00151C0003058	RA0017D0059774	. WINWOOD TEA ESTATE (PVT) LTD	57,237
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00121A0001782	RA0017D0059775	M/S.WINWOOD TEA ESATES (PVT)LTD.,	218,557
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00111C002388	RA0017D0059804	A.L.C.U. LIYANAGE	18,889
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00051C001455	RA0017D0059806	A.L.C.U. LIYANAGE	31,684
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00141F0001089	RA0017D0059819	MS.KANLIT ENTERPRISES (PVT) LTD	32
Debtor Code : 07383 - S.GURUSINGHE	3-Jul-17	31-Aug-17	RA00141F0001086	RA0017D0059822	MS.KANLIT ENTERPRISES (PVT) LTD	10,371
Debtor Code : 07383 - S.GURUSINGHE	5-Jul-17	31-Aug-17	RA00171C0003289	RA0017D0059834	W.A.C PERERA	55,061
Debtor Code : 07383 - S.GURUSINGHE	6-Jul-17	31-Aug-17	RA0010TC002160	RA0017D0059852	MS.UPALI TRADE CENTER	37,151
Debtor Code : 07383 - S.GURUSINGHE	6-Jul-17	31-Aug-17	RA00151B0005845	RA0017D0059855	M.W.S.S. MUNASINGHE	27,043
Debtor Code : 07383 - S.GURUSINGHE	6-Jul-17	31-Aug-17	02RT1D000862	RA0017D0059857	A.G.A.S ALGODA	19,048
Debtor Code : 07383 - S.GURUSINGHE	6-Jul-17	31-Aug-17	RA00141A0002120	RA0017D0059858	K.M.A. WIJEKOON	79,352
Debtor Code : 07383 - S.GURUSINGHE	6-Jul-17	31-Aug-17	RA0015TF0000076	RA0017D0059859	M.K.L. ARIYASENA	2,267
Debtor Code : 07383 - S.GURUSINGHE	6-Jul-17	31-Aug-17	RT04TC001335	RA0017D0059861	A.G.J.N. ALGODA	37,313
Debtor Code : 07383 - S.GURUSINGHE	7-Jul-17	31-Aug-17	RA00121C0002597	RA0017D0059866	K.M. PRIYANTHA KUMARA	12,676
Debtor Code : 07383 - S.GURUSINGHE	7-Jul-17	31-Aug-17	RA0014CF0001181	RA0017D0059871	M.Y.M. ILHAMYEHIYA	51,694
Debtor Code : 07383 - S.GURUSINGHE	7-Jul-17	31-Aug-17	RA0014CF0001185	RA0017D0059874	M.Y.M. ILHAMYEHIYA	12,720
Debtor Code : 07383 - S.GURUSINGHE	10-Jul-17	31-Aug-17	RA00151A0002390	RA0017D0059892	D.P. MALALGODA	867
Debtor Code : 07383 - S.GURUSINGHE	11-Jul-17	31-Aug-17	RA00151B0005866	RA0017D0059919	K.A.R. KODISINGHE.	137,315
Debtor Code : 07383 - S.GURUSINGHE	11-Jul-17	31-Aug-17	RA00161C0003200	RA0017D0059924	W.R. VIDANAGAMAGE	15,469
Debtor Code : 07383 - S.GURUSINGHE	12-Jul-17	31-Aug-17	RA00111C002396	RA0017D0059925	W.R. VIDANAGAMAGE	28,973
Debtor Code : 07383 - S.GURUSINGHE	12-Jul-17	31-Aug-17	RA00091C002196	RA0017D0059940	P WALIWATTE	33,015
Debtor Code : 07383 - S.GURUSINGHE	12-Jul-17	31-Aug-17	RT01TF000032	RA0017D0059943	. KONGAHAWATTE TEA FACTORY	219,253
Debtor Code : 07383 - S.GURUSINGHE	12-Jul-17	31-Aug-17	RA0012CP0000022	RA0017D0059945	. KONGAHAWATTE TEA FACTORY	206,955
Debtor Code : 07383 - S.GURUSINGHE	12-Jul-17	31-Aug-17	RA0008EL000130	RA0017D0059946	. KOONGAHAWATTE TEA FACTORY	99,719
Debtor Code : 07383 - S.GURUSINGHE	14-Jul-17	31-Aug-17	RA00173A0003339	RA0017D0059970	H.P.T. CALDERA	2,500
Debtor Code : 07383 - S.GURUSINGHE	18-Jul-17	31-Aug-17	RA00153C0001576	RA0017D0059993	. WINWOOD TEA ESTATE (PVT) LTD	3,500
Debtor Code : 07383 - S.GURUSINGHE	18-Jul-17	31-Aug-17	RA00151A0002396	RA0017D0059994	P.G.G. HARISHCHANDRA	325,973
Debtor Code : 07383 - S.GURUSINGHE	18-Jul-17	31-Aug-17	RA0009HH000230	RA0017D0059996	D.M.D.B. SAMARAJEEWA	926
Debtor Code : 07383 - S.GURUSINGHE	18-Jul-17	31-Aug-17	RA00171C0003291	RA0017D0059997	G.D.D. SENANI	54,241
Debtor Code : 07383 - S.GURUSINGHE	18-Jul-17	31-Aug-17	03RT1D001175	RA0017D0060011	W.K DHARMASENA	24,247
Debtor Code : 07383 - S.GURUSINGHE	20-Jul-17	31-Aug-17	RA00061C001679	RA0017D0060034	W.W.D.S.R. PERERA	25,725
Debtor Code : 07383 - S.GURUSINGHE	21-Jul-17	31-Aug-17	RA0014CF0001187	RA0017D0060052	J.M.M. JAYASUNDARA	17,074
Debtor Code : 07383 - S.GURUSINGHE	21-Jul-17	31-Aug-17	RA0014TL0000023	RA0017D0060053	J.M.M. JAYASUNDARA	4,120
Debtor Code : 07383 - S.GURUSINGHE	26-Jul-17	31-Aug-17	RA00171B0005908	RA0017D0060093	K.V.D.M. WIMALANATHA	20,700
Debtor Code : 07383 - S.GURUSINGHE	28-Jul-17	31-Aug-17	RA00171A0002880	RA0017D0060136	D.M.J.C.S DASANAYAKA.	97,015
Debtor Code : 07383 - S.GURUSINGHE	28-Jul-17	31-Aug-17	RA00101F000888	RA0017D0060153	M.R.M.P.M. GUNATHILAKA	9,118
Debtor Code : 07383 - S.GURUSINGHE	1-Aug-17	31-Aug-17	RA00141A0002142	RA0017D0060239	W.R.N. GUNASEKARA	36,847
Debtor Code : 07383 - S.GURUSINGHE	1-Aug-17	31-Aug-17	RA00111C002406	RA0017D0060240	W.S.S.S. GUNASENA	34,555
Debtor Code : 07383 - S.GURUSINGHE	1-Aug-17	31-Aug-17	RA00141C0002923	RA0017D0060241	. K.A. SAMARAJEEWA. KANLITH ENTER	18,356
Debtor Code : 07383 - S.GURUSINGHE	1-Aug-17	31-Aug-17	RA00141C0002924	RA0017D0060245	. K.A. SAMARJEEWA, KANLIT ENTERPR	18,356
Debtor Code : 07383 - S.GURUSINGHE	1-Aug-17	31-Aug-17	RA00111C002410	RA0017D0060246	S.S.P.ENGINEERS (PRIVATE) LIMITED	56,313
Debtor Code : 07383 - S.GURUSINGHE	1-Aug-17	31-Aug-17	RA00151B0005858	RA0017D0060247	M.A.U. PRADEEP	28,036
Debtor Code : 07383 - S.GURUSINGHE	1-Aug-17	31-Aug-17	RA00151B0005859	RA0017D0060248	M.A.U. PRADEEP	48,585
Debtor Code : 07383 - S.GURUSINGHE	1-Aug-17	31-Aug-17	RA00151B0005857	RA0017D0060250	M.A.U. PRADEEP	32,158
Debtor Code : 07383 - S.GURUSINGHE	1-Aug-17	31-Aug-17	RA00171A0002881	RA0017D0060258	L.L.A. NANDANA	55,616
Debtor Code : 07383 - S.GURUSINGHE	2-Aug-17	31-Aug-17	RA00171C0003295	RA0017D0060262	M.K.L. ARIYASENA.	346,837
Debtor Code : 07383 - S.GURUSINGHE	2-Aug-17	31-Aug-17	RA00173A0003354	RA0017D0060292	M.A.K. ANURADHA.	2,500
Debtor Code : 07383 - S.GURUSINGHE	4-Aug-17	31-Aug-17	RA0015TF0000076	RA0017D0060315	M.K.L. ARIYASENA	395,332
Debtor Code : 07383 - S.GURUSINGHE	4-Aug-17	31-Aug-17	RA00161W0000173	RA0017D0060317	S. MOHOMAD NAWAZ	10,000
Debtor Code : 07383 - S.GURUSINGHE	4-Aug-17	31-Aug-17	RA0015CP0000027	RA0017D0060318	M.K.L. ARIYASENA	205,656
Debtor Code : 07383 - S.GURUSINGHE	4-Aug-17	31-Aug-17	RA0007TF000050	RA0017D0060332	U. LIYANAGE CHANDANA MITHSEN	313,956
Debtor Code : 07383 - S.GURUSINGHE	4-Aug-17	31-Aug-17	RA0007CP000010	RA0017D0060334	U. LIYANAGE CHANDANA MITHSEN	39,448
Debtor Code : 07383 - S.GURUSINGHE	4-Aug-17	31-Aug-17	RT01TF000032	RA0017D0060335	. KONGAHAWATTE TEA FACTORY	12,337
Debtor Code : 07383 - S.GURUSINGHE	4-Aug-17	31-Aug-17	RA0012CP0000022	RA0017D0060336	. KONGAHAWATTE TEA FACTORY	23,835
Debtor Code : 07383 - S.GURUSINGHE	5-Aug-17	31-Aug-17	RA00171C0003296	RA0017D0060372	M.G.I.R. WICKRAMASINGHE	74,110
Debtor Code : 07383 - S.GURUSINGHE	8-Aug-17	31-Aug-17	RA0012EL0000164	RA0017D0060376	THUSHAN UPASENA	94,466
Debtor Code : 07383 - S.GURUSINGHE	8-Aug-17	31-Aug-17	RA00151W0000088	RA0017D0060407	D.P.G. PRIYANKARA	6,313
Debtor Code : 07383 - S.GURUSINGHE	9-Aug-17	31-Aug-17	RA00141X0001246	RA0017D0060421	W.M.T. BUDDIKA BANDARA	7,152
Debtor Code : 07383 - S.GURUSINGHE	9-Aug-17	31-Aug-17	RA00171A0002884	RA0017D0060435	S. ABEYGUNARATNE.	52,941
Debtor Code : 07383 - S.GURUSINGHE	10-Aug-17	31-Aug-17	RA0015FZ0000213	RA0017D0060469	S.S.P. ENGINEERS (PVT) LTD.,	71,007
Debtor Code : 07383 - S.GURUSINGHE	10-Aug-17	31-Aug-17	RA0015CF0001277	RA0017D0060473	S.D.A. INDRATHILAKA	42,274
Debtor Code : 07383 - S.GURUSINGHE	11-Aug-17	31-Aug-17	RA00141A0002149	RA0017D0060475	D.H.D.U. INDRARATNE	94,623
Debtor Code : 07383 - S.GURUSINGHE	11-Aug-17	31-Aug-17	RA00161A0002670	RA0017D0060476	M.C. HATHARASINGHE	47,844
Debtor Code : 07383 - S.GURUSINGHE	11-Aug-17	31-Aug-17	RA00071B002933	RA0017D0060477	B.G.N BODARAGAMA	46,843
Debtor Code : 07383 - S.GURUSINGHE	11-Aug-17	31-Aug-17	RA00101D002940	RA0017D0060485	M.A.N. WICKRAMASINGHE	19,238
Debtor Code : 07383 - S.GURUSINGHE	15-Aug-17	31-Aug-17	RA00153C0001594	RA0017D0060512	M.D. ARIYASENA	3,800
Debtor Code : 07383 - S.GURUSINGHE	15-Aug-17	31-Aug-17	RA00153C0001593	RA0017D0060513	M.D. ARIYASENA	3,800
Debtor Code : 07383 - S.GURUSINGHE	15-Aug-17	31-Aug-17	RA00153D0000660	RA0017D0060514	M.D. ARIYASENA	3,800
Debtor Code : 07383 - S.GURUSINGHE	15-Aug-17	31-Aug-17	RT01CF00712	RA0017D0060520	K.D.N.T.K. UPASENA	17,952
Debtor Code : 07383 - S.GURUSINGHE	15-Aug-17	31-Aug-17	RA00171F0001157	RA0017D0060526	CEYLINCO GENERAL INSURANCE LTD..	12,250
Debtor Code : 07383 - S.GURUSINGHE	15-Aug-17	31-Aug-17	03RT1C001049	RA0017D0060527	H.G DHARMAWARDANA	23,409
Debtor Code : 07383 - S.GURUSINGHE	16-Aug-17	31-Aug-17	RA00163B0002556	RA0017D0060536	M.D. ARIYASENA	3,300
Debtor Code : 07383 - S.GURUSINGHE	16-Aug-17	31-Aug-17	RA00153C0001598	RA0017D0060537	M.K.L. ARIYASENA	3,800
Debtor Code : 07383 - S.GURUSINGHE	16-Aug-17	31-Aug-17	RA0012TF0000067	RA0017D0060541	K.D.N.T.K. UPASENA	25,601
Debtor Code : 07383 - S.GURUSINGHE	17-Aug-17	31-Aug-17	RA0009TF000054	RA0017D0060548	M.K.L. ARIYASENA	599,555
Debtor Code : 07383 - S.GURUSINGHE	18-Aug-17	31-Aug-17	RA0015CP0000029	RA0017D0060558	M.K.L. ARIYASENA	188,175
Debtor Code : 07383 - S.GURUSINGHE	18-Aug-17	31-Aug-17	RA00153C0001595	RA0017D0060560	M.D. ARIYASENA	3,800
Debtor Code : 07383 - S.GURUSINGHE	18-Aug-17	31-Aug-17	RA0012TF0000067	RA0017D0060561	K.D.N.T.K. UPASENA	47,335
Debtor Code : 07383 - S.GURUSINGHE	18-Aug-17	31-Aug-17	RA00161A0002662	RA0017D0060564	S.A. THISSA JAYAWARDHANA	798,278
Debtor Code : 07383 - S.GURUSINGHE	21-Aug-17	31-Aug-17	RA0010DF000898	RA0017D0060574	D.S. PUNCHIHEWA & MR.P.L.PUNCHIHE	9,228
Debtor Code : 07383 - S.GURUSINGHE	21-Aug-17	31-Aug-17	RA00131A0001944	RA0017D0060576	K.A.U. LALANTHA	44,751
Debtor Code : 07383 - S.GURUSINGHE	21-Aug-17	31-Aug-17	04RT1D001568	RA0017D0060579	W.K. DARMASENA	68,898
Debtor Code : 07383 - S.GURUSINGHE	21-Aug-17	31-Aug-17	RA00141A0002160	RA0017D0060580	W.M.G.U. WIJESUNDARA.	44,095
Debtor Code : 07383 - S.GURUSINGHE	21-Aug-17	31-Aug-17	RA00131D0003484	RA0017D0060582	A.K. MILTON SILVA.	48,454
Debtor Code : 07383 - S.GURUSINGHE	21-Aug-17	31-Aug-17	RA00141C0002937	RA0017D0060583	S.S.P. ENGINEERS (PVT) LTD.,	94,633
Debtor Code : 07383 - S.GURUSINGHE	22-Aug-17	31-Aug-17	RA00161C0003186	RA0017D0060587	B.A.R.U. NISANSALA	39,547
Debtor Code : 07383 - S.GURUSINGHE	22-Aug-17	31-Aug-17	RA00091D002819	RA0017D0060588	L.H SWARNALATHA	50,428
Debtor Code : 07383 - S.GURUSINGHE	22-Aug-17	31-Aug-17	RA00141C0002933	RA0017D0060592	S.S.P. ENGINEERS (PVT) LTD.,	114,937
Debtor Code : 07383 - S.GURUSINGHE	23-Aug-17	31-Aug-17	RA00171F0001158	RA0017D0060599	CEYLINCO GENERAL INSURANCE LTD...	12,250
Debtor Code : 07383 - S.GURUSINGHE	23-Aug-17	31-Aug-17	RA00171F0001159	RA0017D0060600	CEYLINCO GENERAL INSURANCE LTD.	12,250
Debtor Code : 07383 - S.GURUSINGHE	23-Aug-17	31-Aug-17	RA00171F0001160	RA0017D0060601	CEYLINCO GENERAL INSURANCE LTD.	12,250
Debtor Code : 07383 - S.GURUSINGHE	23-Aug-17	31-Aug-17	RA00171F0001161	RA0017D0060602	CEYLINCO GENERAL INSURANCE LTD.	12,250
Debtor Code : 07383 - S.GURUSINGHE	24-Aug-17	31-Aug-17	RA00171X0002563	RA0017D0060613	J.M.S.K. BANDARA	7,908
Debtor Code : 07383 - S.GURUSINGHE	25-Aug-17	31-Aug-17	RA00153D0000661	RA0017D0060621	M.D. ARIYASENA	3,800
Debtor Code : 07383 - S.GURUSINGHE	25-Aug-17	31-Aug-17	RA00091B003517	RA0017D0060622	K.T.J MAHESH	38,360
Debtor Code : 07383 - S.GURUSINGHE	25-Aug-17	31-Aug-17	RA00121X0000020	RA0017D0060625	N.J. RANASINGHE	11,433
Debtor Code : 07383 - S.GURUSINGHE	27-Aug-17	31-Aug-17	RA00173F0021123	RA0017D0060632	E.V.U.S. KUMARA	1,000
Debtor Code : 07639 - M.T.N.A.KUMARA	28-Jun-17	31-Aug-17	RA00071D002523	RA0017D0059699	N. BULUTOTA	49,807
Debtor Code : 07639 - M.T.N.A.KUMARA	17-Jul-17	31-Aug-17	04RT1D001531	RA0017D0059972	M.G.P.N. KULATHUNGA.	7,978
Debtor Code : 07639 - M.T.N.A.KUMARA	31-Jul-17	31-Aug-17	03RT1B000736	RA0017D0060179	W.K CHANDRADASA	46,171
Debtor Code : 07639 - M.T.N.A.KUMARA	14-Aug-17	31-Aug-17	RA00051B001681	RA0017D0060507	M.A.P. RUPASINGHE	44,478
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	30-Jun-17	31-Aug-17	RA0017HB0000629	RA0017D0059742	P.M.N.S. DAYANANDA	1,953
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	10-Jul-17	31-Aug-17	RA00141X0001109	RA0017D0059888	S.C. SOMASIRI	5,395
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	11-Jul-17	31-Aug-17	RA00164F0000315	RA0017D0059901	R.M.R. MALKANTHI	9,848
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	11-Jul-17	31-Aug-17	RA0016SE0001267	RA0017D0059921	R.S. RANATHUNGA	1,500
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	11-Jul-17	31-Aug-17	RA0016SE0001234	RA0017D0059922	U.T. PUSHPA KUMARA	1,500
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	11-Jul-17	31-Aug-17	RA0017SE0001419	RA0017D0059923	T. INDRALAL	1,500
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	12-Jul-17	31-Aug-17	RA0009TC002042	RA0017D0059941	K.G. LALITH KUMARA	29,079
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	17-Jul-17	31-Aug-17	RA00161A0002655	RA0017D0059973	G.H.T.N. YASARATHNE.	66,974
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	17-Jul-17	31-Aug-17	RA0016SE0001264	RA0017D0059977	R.G.S. KANTHI	1,000
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	17-Jul-17	31-Aug-17	RA0016SE0001284	RA0017D0059988	M.M.A.S. PEREREA	1,500
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	17-Jul-17	31-Aug-17	RA0016SE0001278	RA0017D0059989	M.M.N. PERERA	1,500
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	19-Jul-17	31-Aug-17	RA0017PH0003227	RA0017D0060021	J.S.I. NILANTHI	1,500
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	19-Jul-17	31-Aug-17	RA0017SE0001424	RA0017D0060022	J.S.I. NILANTHI	1,500
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	21-Jul-17	31-Aug-17	RA0016PH0002953	RA0017D0060055	S.S. SENARATHNA	1,500
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	24-Jul-17	31-Aug-17	RA0016SE0001245	RA0017D0060066	A.A.D.R. PREMATHILAKA	1,500
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	24-Jul-17	31-Aug-17	RA0017SE0001428	RA0017D0060067	D.M.S. JAYALATH	1,500
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	26-Jul-17	31-Aug-17	RA0015CF0001273	RA0017D0060087	J. DAMIYAN	3,709
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	26-Jul-17	31-Aug-17	RA0015HB0000576	RA0017D0060088	J. DAMIYAN	3,709
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	26-Jul-17	31-Aug-17	RA0010PH001241	RA0017D0060098	W.D. UPUL PRIYANTHA	1,000
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	26-Jul-17	31-Aug-17	RA0017OM0000721	RA0017D0060105	L.A. CHATHURA MADUSHAN	2,500
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN	27-Jul-17	31-Aug-17	RA0017PH0003232	RA0017D0060118	W.N. INDUNIL	1,000
Debtor Code : 08593 - MR. L.A. CHATHURA MADUSHAN

sunny_talwar · ‎2017-09-03

Great please close this thread now

Qlik Community Tip: Marking Replies as Correct or Helpful

View solution in original post

sunny_talwar · ‎2017-08-31

Try this

Table:

LOAD DEBTOR,

Trim(TextBetween(DEBTOR, ':', '-')) as DEBTOR_CODE,

PurgeChar(Trim(SubField(DEBTOR, '-', -1)), Chr(160)) as DEBTOR_NAME,

RISK_DATE,

AS_AT_DATE,

POL_NUMBER,

REF_NUMBER,

INSURED,

AMOUNT

FROM

[..\..\Downloads\DEBTORS.xlsx]

(ooxml, embedded labels, table is Sheet1);

sunny_talwar · ‎2017-08-31

Or this

Table:

LOAD DEBTOR,

Trim(PurgeChar(TextBetween(DEBTOR, ':', '-'), Chr(160))) as DEBTOR_CODE,

Trim(PurgeChar(SubField(DEBTOR, '-', -1), Chr(160))) as DEBTOR_NAME,

RISK_DATE,

AS_AT_DATE,

POL_NUMBER,

REF_NUMBER,

INSURED,

AMOUNT

FROM

[..\..\Downloads\DEBTORS.xlsx]

(ooxml, embedded labels, table is Sheet1);

nevilledhamsiri · ‎2017-08-31

Thank for your reply, but it seems working for few cases such as what you have highlighted. Please check following cases as an example!

2019-039

peoples leasing & finance PLCBAL

How the code & name appear in the original data, but check them after being loaded which appears as below

code as 2019, name as BAL.

I check some more cases which are having longer lengths all of them not get the full code & names.

Appreciate if this is looked in to.

LOAD DEBTOR,

TRIM(TEXTBETWEEN(DEBTOR,':','-')) AS DEBTOR_CODE,

PURGECHAR(TRIM(SUBFIELD(DEBTOR,'-',-1)),CHR(160)) AS DEBTOR_NAME,

RISK_DATE,

AS_AT_DATE,

POL_NUMBER,

REF_NUMBER,

INSURED,

AMOUNT,

H

FROM

[..\DEBTORS.xlsx]

(ooxml, embedded labels, table is [DEB(RA)]);

sunny_talwar · ‎2017-09-01

This is what is available in DEBOR

2019-039

peoples leasing & finance PLCBAL

What would you like to see in DEBOR_CODE and DEBTOR_NAME? Also, can you share more variations because I cannot give you a solution for all cases unless I know the values that might come through

upaliwije · ‎2017-09-02

Nevil,

Create a separate File under following headings and link the same to your data file

Debtor_code Debtor_name

nevilledhamsiri · ‎2017-09-03

Thanks Mr Upali,

What you suggested I have applied & got the results. Simply what I wanted was to achieve the results as it is.What Sunny proposed did work 90% but for few cases it appears to be not working!. Thanks all guys I could manage this.

sunny_talwar · ‎2017-09-03

Great please close this thread now

Qlik Community Tip: Marking Replies as Correct or Helpful

Extraction of codes & names seperately from a long list