Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

mid function in two different character field

how can I use mid function in two different character field. I got it one

mid('policy_no',7,2) as 'class_code',

also I need

mid(policy_no,5,2) as class_code,

need to combine. . .

tnkz

1 Solution

Accepted Solutions

Re: Re: mid function in two different character field

Hi,

Try this in your Qlikview file.

Load *, if (len(POLICY_NO)=14,mid(POLICY_NO,7,2),mid(POLICY_NO,5,2)) as CLASS_CODE;

load * Inline

[

POLICY_NO

KP0010SA006957 

KP0010SA006954

KP02CF000466

KP05DF000307

];

Let me know if that worked.

Regards

ASHFAQ

9 Replies

Re: mid function in two different character field

HI,


try like this


mid('policy_no',7,2)&'_'&mid(policy_no,5,2) as Newcode


Regards

ASHFAQ



MVP
MVP

Re: mid function in two different character field

use & for string concatenation

mid(policy_no,7,2)  & mid(policy_no,5,2) as class_code,

Not applicable

Re: mid function in two different character field

following is my field (policy_no). I need to create new field (class_code).

POLICY_NOCLASS_CODE
KP0010SA006957 
SA
.=MID(D18,7,2)
KP0010SA006954SA
KP02CF000466CF.=MID(D18,5,2)
KP05DF000307DF

please check.

Thanks for your co-operation,

Dhanushka.

Re: mid function in two different character field

Hi,

Is this policy number a constant fixed value?

Regards

ASHFAQ

Re: Re: mid function in two different character field

Hi,

Try this in your Qlikview file.

Load *, if (len(POLICY_NO)=14,mid(POLICY_NO,7,2),mid(POLICY_NO,5,2)) as CLASS_CODE;

load * Inline

[

POLICY_NO

KP0010SA006957 

KP0010SA006954

KP02CF000466

KP05DF000307

];

Let me know if that worked.

Regards

ASHFAQ

stigchel
Honored Contributor

Re: mid function in two different character field

It is difficult to say if the following expression will apply to all cases but this works for your sample data:

Mid(PurgeChar(POLICY_NO,'0123456789'),3,2) as CLASS_CODE

See also attached

stigchel
Honored Contributor

Re: mid function in two different character field

What I do here is removing all numbers from the Policy_NO, after which you have 'KP' and after that your class code (if I understand you correctly) the mid (...3,2) takes the 2 characters after that.....

Not applicable

Re: mid function in two different character field

I want to pick middle two carters as Class_code.

But its not in same place. Two types of character field.

Thanks,

Dhanushka.

Not applicable

Re: Re: mid function in two different character field

Thankz Ashfaq,

Its working..........

I made it as follows,

POLICY_NO,

  if (len(POLICY_NO)=14,mid(POLICY_NO,7,2),mid(POLICY_NO,5,2)) as CLASS_CODE,

Thanks again,

Dhanushka.

Community Browser