topic Right, Left, Mid in the script in QlikView

Right, Left, Mid in the script

Mon, 11 Apr 2011 09:36:33 GMT

Hi,

I have a KeyField with the Material Number and Plant as below.

999999020-0310

How can I seperate the two Fields. The Material Number length is not fixed. it can be from 3 Digits to more than 10 or more.

I can get the Plant Number with this code

right(Field,4) as Plant

Somecombination of right() ands left() shoould give me result. Can anyone help

Thanks

Sravan

Right, Left, Mid in the script

Mon, 11 Apr 2011 09:44:44 GMT

You can get it using below exp

left(Field,len(Field)-right(Field,5))

Thanks & Reagrds

Right, Left, Mid in the script

IAMDV — Mon, 11 Apr 2011 09:54:25 GMT

Sravan - Use the SUBFIELD function and split by delimiter.

=SubField(FieldName, '-')

Hope this helps...

Cheers - DV

Right, Left, Mid in the script

Mon, 11 Apr 2011 10:18:21 GMT

Hi Upendra,

with little correction, your code worked. Thanks:)

left(field,len(field)-len(right(field,5)))

@ deepak,

with Subfield I get the field split in to Material and Plant but I cant apply left() or right() to get the fields

Anyway thanks

sravan

Right, Left, Mid in the script

IAMDV — Mon, 11 Apr 2011 10:23:46 GMT

Excellent! I am glad that you got it working.

Just a quick word of caution! I am very sure you are aware of this... If the number of characters in the Plant Field changes in coming years, you might have incomplete data. If this is case then I would recommend making the function more robust.

Cheers - DV

Right, Left, Mid in the script

Mon, 11 Apr 2011 10:32:39 GMT

Thanks Deepak,

I know that but I hope that there is a better solution than this! For Now I will take this.

Regards

Sravan

Right, Left, Mid in the script

nathanfurby — Mon, 11 Apr 2011 10:37:47 GMT

DV is correct. You should use the SubField function. It's much easier and also more robust:

SubField(Field,'-',1) AS MaterialNumber

SubField(Field,'-',2) AS Plant

Right, Left, Mid in the script

IAMDV — Mon, 11 Apr 2011 10:41:26 GMT

Sravan - Just thought this might be more robust solution.

To get the Material Number use this expression:

=Left(UniqueNumber, FindOneOf(UniqueNumber, '-') - 1)

To get the Plant Number use this expression:

=Right(UniqueNumber, Len(UniqueNumber) - FindOneOf(UniqueNumber, '-'))

With these expressions you don't have to worry about the number of characters. I mean you are not dependant on the number of characters in the field, rather you will be using the properties of the Field.

I hope this is clear enough for your understanding. Let me know if you need further explanation.

Cheers - DV

Right, Left, Mid in the script

IAMDV — Mon, 11 Apr 2011 10:45:34 GMT

Thanks NathanFurby.

I thought the same! It is good practice to use these kind of expression in the Load Script compared to the Field expressions.

Cheers - DV

Right, Left, Mid in the script

nathanfurby — Mon, 11 Apr 2011 10:56:24 GMT

Yep - no need for complicated string functions. Subfield will do the job

Right, Left, Mid in the script

Tue, 12 Apr 2011 13:47:19 GMT

Thanks Guys:)