mid function in two different character field

[#1]

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

[ashfaq_haseeb]

HI,

try like this

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

Regards

ASHFAQ

[maxgro]

use & for string concatenation

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

[#4]

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

POLICY_NO	CLASS_CODE
KP0010SA006957	SA	.=MID(D18,7,2)
KP0010SA006954	SA
KP02CF000466	CF	.=MID(D18,5,2)
KP05DF000307	DF

please check.

Thanks for your co-operation,

Dhanushka.

[ashfaq_haseeb]

Hi,

Is this policy number a constant fixed value?

Regards

ASHFAQ

[ashfaq_haseeb · Accepted solution]

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]

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]

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.....

[#9]

I want to pick middle two carters as Class_code.

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

Thanks,

Dhanushka.

[#10]

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.