topic Re: mid function in two different character field in Qlik Learning Discussions

mid function in two different character field

Sun, 19 Oct 2014 12:00:26 GMT

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

Re: mid function in two different character field

ashfaq_haseeb — Sun, 19 Oct 2014 14:01:17 GMT

HI,

try like this

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

Regards

ASHFAQ

Re: mid function in two different character field

maxgro — Sun, 19 Oct 2014 14:19:59 GMT

use & for string concatenation

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

Re: mid function in two different character field

Mon, 20 Oct 2014 16:16:23 GMT

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

POLICY_NO

CLASS_CODE

KP0010SA006957

.=MID(D18,7,2)

KP0010SA006954

KP02CF000466

.=MID(D18,5,2)

KP05DF000307

please check.

Thanks for your co-operation,

Dhanushka.

Re: mid function in two different character field

ashfaq_haseeb — Mon, 20 Oct 2014 16:20:51 GMT

Hi,

Is this policy number a constant fixed value?

Regards

ASHFAQ

Re: Re: mid function in two different character field

ashfaq_haseeb — Mon, 20 Oct 2014 16:24:22 GMT

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

Re: mid function in two different character field

stigchel — Mon, 20 Oct 2014 16:45:19 GMT

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

Re: mid function in two different character field

stigchel — Mon, 20 Oct 2014 16:48:21 GMT

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

Re: mid function in two different character field

Mon, 20 Oct 2014 17:01:46 GMT

I want to pick middle two carters as Class_code.

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

Thanks,

Dhanushka.

Re: Re: mid function in two different character field

Mon, 20 Oct 2014 17:10:16 GMT

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.