topic subfield&replace function in QlikView

subfield&replace function

Wed, 24 Jun 2015 07:14:12 GMT

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

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

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

Re: Replace function

fvelascog72 — Wed, 24 Jun 2015 07:37:00 GMT

Hi, Try this:

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

Re: Replace function

qlikviewwizard — Wed, 24 Jun 2015 07:41:24 GMT

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;

Re: Replace function

SatyaPaleti — Wed, 24 Jun 2015 07:44:24 GMT

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

Re: Replace function

settu_periasamy — Wed, 24 Jun 2015 08:40:25 GMT

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.

Re: Replace function

Wed, 24 Jun 2015 09:05:12 GMT

Hi,

I have done using substringcount at the script level.

Please find the attached QVW.

Regards

Re: Replace function

senpradip007 — Wed, 24 Jun 2015 09:12:08 GMT

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

];

Re: Replace function

Wed, 24 Jun 2015 12:31:11 GMT

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

Re: Replace function

buzzy996 — Wed, 24 Jun 2015 12:52:20 GMT

try some thing like this,

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

Re: Replace function

Wed, 24 Jun 2015 16:47:07 GMT

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

Re: Replace function

settu_periasamy — Wed, 24 Jun 2015 17:30:15 GMT

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

Re: Replace function

sasiparupudi1 — Wed, 24 Jun 2015 17:53:39 GMT

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

];

Re: Replace function

tamilarasu — Thu, 25 Jun 2015 03:03:17 GMT

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.

Re: Replace function

settu_periasamy — Thu, 25 Jun 2015 03:09:16 GMT

Hi Naveen,

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

Re: Replace function

Thu, 25 Jun 2015 03:25:37 GMT

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

Re: Replace function

tamilarasu — Thu, 25 Jun 2015 03:30:13 GMT

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.

Re: Replace function

Thu, 25 Jun 2015 03:34:49 GMT

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;

Re: Replace function

jsingh71 — Thu, 25 Jun 2015 04:58:27 GMT

Hi Naveen,

Please find attached qvw as my solution.

--Jai

Re: Replace function

Thu, 25 Jun 2015 14:40:58 GMT

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

Re: Replace function

settu_periasamy — Thu, 25 Jun 2015 14:59:51 GMT

There is a button above 'Actions'.