Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

subfield&replace function

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

24 Replies
fvelascog72
Partner
Partner

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
Master II
Master II

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;

1.JPG

2.JPG

SatyaPaleti
Creator III
Creator III

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.

Capture.JPG

Not applicable
Author

Hi,

I have done using substringcount at the script level.

Please find the attached QVW.

Regards

senpradip007
Specialist III
Specialist III

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   

];      

Not applicable
Author

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

buzzy996
Master II
Master II

try some thing like this,


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

Not applicable
Author

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