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

[settu_periasamy]

Hi,

try this in script

Pick (Match ((Subfield([Claim Number],'/',2)),
'TML','AP','KAR'),'Tamilnadu,','Andhrapradesh','Karnataka') as city

Edit: forgot to add parantesis infront of Subfield

[sasiparupudi1]

StateMap:

Mapping LOAD * Inline

[

x,y

AP,AndhraPradesh

TML,TamilNadu

KAR,Karnatka

];

tabQuarters:

load

  SubField(Claim_Number, '/', 1)&'/'&ApplyMap ('StateMap',SubField(Claim_Number, '/', 2),SubField(Claim_Number, '/', 2))&'/'&SubField(Claim_Number, '/', 3)&'/'&if (Len(SubField(Claim_Number, '/', 4))=2,'20'&SubField(Claim_Number, '/', 4),SubField(Claim_Number, '/', 4)) as Claim_Number

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

];

[tamilarasu]

Hi Naveen,

You can try this also.

States: 
Mapping load * Inline [ 
ShortName, FullName 
AP, AndhraPrdesh
TML, TamilaNadu 
KAR, Karnataka] ; 


Table: 
Load *,
MapSubString('States',[ClaimNumber]) as FormattedClaimNumber;


LOAD *, 
if(Len(SubField([Claim_Number],'/',4))<3,left([Claim_Number], len([Claim_Number])-2)&'20'&SubField([Claim_Number],'/',4),[Claim_Number]) as ClaimNumber


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    
];

See the attachment.

Let me know if that works for you.

[settu_periasamy]

Hi Naveen,

Can you close this thread if you got the answer? or still you need something ?

[#6]

its working thank u friend..........

[tamilarasu]

Hi Naveen,

If you got answer, please close the thread by giving 'Correct answer' option to the reply which works for you. So that someone will use the solution in future.

[#8]

Hi Naveen

Try This

Claim:

LOAD [Claim Number]

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

NewClaim:

Load 

Replace([Claim Number],'/11','/2011') as NewNumber

Resident Claim;

Drop Table Claim;

[jsingh71]

Hi Naveen,

Please find attached qvw as my solution.

--Jai

[#10]

hi...i can seeing helpful button only thats why..where i can find correct answer button

[settu_periasamy]

There is a button above 'Actions'.