subfield&replace function

[#1]

friends, in my table i have a field name [Claim number],fields values are like OIC/KAR/10983/2012

Claim number
OIC/AP/3950/2011 OIC/TML/3951/11 OIC/AP/3953/11 OIC/AP/3952/2012 OIC/KAR/3953/12 OIC/AP/3954/12 OIC/AP/3953/2013 OIC/AP/3953/13

now from Claim number i want replace 11 as a 2011 but i want to keep that 2011 as same

i  use

LOAD Name,

Replace([Claim Number],11,2011)as Year,

[Approval Amount] Resident insurance;

Now it will show like this

Year
OIC/AP/3950/202011 OIC/AP/3951/2011 OIC/AP/3953/2011

how i can achieve

i have 2000 records inthat

Message was edited by: Naveen Reddy

[MarcoWedel]

Hi,

another solution could be:

tabClaims:
LOAD *,
    Left([Claim Number],Index([Claim Number],'/',-1))&Year(Date#(SubField([Claim Number],'/',-1),'YY')) as ClaimNumber2
Inline [
Claim Number
OIC/AP/3950/2011
OIC/TML/3951/11
OIC/AP/3953/11
OIC/AP/3952/2012
OIC/KAR/3953/12
OIC/AP/3954/12
OIC/AP/3953/2013
OIC/AP/3953/13
];

hope this helps

regards

Marco

P.S.: please open new threads as question (if they are questions), so you are able to select correct answers.

[#3]

Thank u Marco Wedel

[SatyaPaleti]

Naveen,

Are u looking this kind of formatt

OIC/AP/3950/2011  --> OIC/AndhrPradesh/3950/2011

OIC/TML/3951/11   --> OIC/Tamilnadu/3951/2011 

OIC/AP/3953/11   -->  OIC/AndhrPradesh/3953/2011

OIC/AP/3952/2012  --> OIC/AndhrPradesh/3952/2012

OIC/KAR/3953/12   --> OIC/Karnataka/3953/2012

OIC/AP/3954/12    --> OIC/AndhrPradesh/3954/2012 

OIC/AP/3953/2013  --> OIC/AndhrPradesh/3953/2013

[#5]

yes mr sathya

[SatyaPaleti]

Naveen Try with this

if(SubField(Claim Number,'/',2)= 'AP',SubField(Claim Number,'/',1)&'/'&''&Replace(SubField(Claim Number,'/',2),'AP','AndhraPradesh')&''&Mid(ClaimYear,7),Claim Number,

  if(SubField(Claim Number,'/',2)= 'TML',SubField(Claim Number,'/',1)&'/'&''&Replace(SubField(Claim Number,'/',2),'TML','Tamilnadu')&''&Mid(ClaimYear,8),Claim Number,

  if(SubField(Claim Number,'/',2)= 'KAR',SubField(Claim Number,'/',1)&'/'&''&Replace(SubField(Claim Number,'/',2),'KAR','Karnataka')&''&Mid(ClaimYear,8),Claim Number,

  )

  )

  ) as ClaimState

Thank you,

Satya Paleti