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

[fvelascog72]

Hi, Try this:

if(len(SubField([Claim number],'/',4))>2,[Claim number],Replace([Claim number],Right([Claim number],2),'20'&Right([Claim number],2)))

[qlikviewwizard]

Hi Use this script:

Table:

load * 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

];

load * ,

replace([Claim Number],11,2011) as NewClaimNumber

Resident Table;

[SatyaPaleti]

Hi Naveen,

Better try this logic it will works

if(len(SubField([Claim number],'/',4))>2,[Claim number],Replace([Claim number],Right([Claim number],2),'20'&Right([Claim number],2)))

Thank you,

Satya paleti

[settu_periasamy]

Hi,

Try the logic said by fvelascog72

Just i added the right function to get the year.

Table:  
LOAD * 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  
];     
Table1:  
LOAD if(len(SubField([Claim Number],'/',4))=2,Right(Replace([Claim Number],Right([Claim Number],2),'20'&Right([Claim Number],2)),4)  
,Right(Replace([Claim Number],Right([Claim Number],2),'20'&Right([Claim Number],2)),4)) as Year,  
if(len(SubField([Claim Number],'/',4))>2,[Claim Number],Replace([Claim Number],Right([Claim Number],2),'20'&Right([Claim Number],2))) as ClaimNumber  
Resident Table  ;  
DROP Table Table; 

see the screen shot.

[#6]

Hi,

I have done using substringcount at the script level.

Please find the attached QVW.

Regards

[senpradip007]

Use this.

Table:   

LOAD *,

if(len(SubField(Claim_Number, '/', 4))=2, 2000+num(SubField(Claim_Number, '/', 4)), SubField(Claim_Number, '/', 4)) as New

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   

];      

[#8]

This is what exact i want.......thank u settu_periasamy

[buzzy996]

try some thing like this,

if(Match(SubField([Claim number],'/',4),'11'),'2011',urfield)

[#10]

and i want to bring out that TM,AP,KAR as tamilanadu,andhraprdesh,karnataka