topic String Manipulation in QlikView

String Manipulation

Wed, 11 Apr 2012 07:43:06 GMT

Hi All,

I have a field that consist of this kind of values, i want to create a new field with the following conditions:

Field1	rowid
BELL, B150145, BLACK, BIN J	1
BELL, B150145, WHITE A, BIN H, SnO2	2
BELL, C160135, WHITE A, BIN E	3
BELL, C150145, WHITE B, MASTER BIN	4
BELL, C160145, BLACK, BIN D, Aurora	5
BELL, C160145, MASTER BIN, Aurora	6
CELL, C160145, BIN TS	7
PVM, SPR-215-WHT-U N	8

First I want to know the field values that has the word BIN then i want to get the letter/s after the word BIN, for example for rowids:

1=J, 2=H, 3=E, 5=D, 7=TS. the letter or letters after the word BIN should be only 1 or 2. (the letters will be the value of the new field)

if field1 has the word BIN on it but the latter part is not a single letter or a 2 letter combination like rowid: 4 and 6, this is an invalid Field1 value.

then if Field1 column doesn't consist of the word BIN we will not get this field value.

I want to do this in the load script

I hope you can help me on this.. Thank you in advance!!

BELL, B150145, BLACK, BIN J

Re: String Manipulation

adhudson — Wed, 11 Apr 2012 08:11:08 GMT

Hi,

I have attched the solution file below.

It matches your requirement.

Hope this helps you.

Regards

Andrew Hudson

Re: String Manipulation

CELAMBARASAN — Wed, 11 Apr 2012 08:53:56 GMT

Hi,

Try using this expression for the result field

Trim(SubField(SubField(Field1,'BIN',2),',')) as ResultField

Celambarasan

Re: String Manipulation

Wed, 11 Apr 2012 10:48:53 GMT

i have solved your problem...yoy can have a look at the screen shot..if it is what you are looking for i will provide yoy the solution.

i have given the field values a name 'y' if it contains bin and if not then 'n'.

also calculated the characters after the BIN