Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
ashfaq_haseeb
Champion III
Champion III

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

View solution in original post

9 Replies
ashfaq_haseeb
Champion III
Champion III

HI,


try like this


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


Regards

ASHFAQ



maxgro
MVP
MVP

use & for string concatenation

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

Not applicable
Author

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.

ashfaq_haseeb
Champion III
Champion III

Hi,

Is this policy number a constant fixed value?

Regards

ASHFAQ

ashfaq_haseeb
Champion III
Champion III

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

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

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
Author

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
Author

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.