Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

How to remove text after a specific character ('.' or ',' or '-')

Hello,

Let's say I have a field with reference numbers.

237589

573830.098.9

98374.09

987464.1

84736.999.1

98383

6444.87

I would like to keep only the numbers before the first dot (.)

237589

573830.098.9

98374.09

987464.1

84736.999.1

98383

6444.87

Thank you

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: How to remove text after a specific character ('.' or ',' or '-')

Try using the Subfield() function:

LOAD *,

Subfield(Test,'.',1) as Extract

INLINE [

Test

237589

573830.098.9

98374.09

987464.1

84736.999.1

98383

6444.87

];

Test Extract
6444.876444
84736.999.184736
98374.0998374
573830.098.9573830
987464.1987464
9838398383
237589237589

View solution in original post

4 Replies
Highlighted
MVP
MVP

Re: How to remove text after a specific character ('.' or ',' or '-')

Try using the Subfield() function:

LOAD *,

Subfield(Test,'.',1) as Extract

INLINE [

Test

237589

573830.098.9

98374.09

987464.1

84736.999.1

98383

6444.87

];

Test Extract
6444.876444
84736.999.184736
98374.0998374
573830.098.9573830
987464.1987464
9838398383
237589237589

View solution in original post

Not applicable

Re: How to remove text after a specific character ('.' or ',' or '-')

Thank you Stefan

Re: How to remove text after a specific character ('.' or ',' or '-')

May be try this:

Table:

LOAD *,

  Left(Number, FindOneOf(Number & '.', '.,-')-1) as NewNumber;

LOAD * Inline [

Number

237589

573830.098.9

98374,09

987464-1

84736.999.1

98383

6444-87

];


Capture.PNG

vishsaggi
Esteemed Contributor III

Re: How to remove text after a specific character ('.' or ',' or '-')

Try this:

LOAD *,

   IF(Index(Num,'.')>=1, Subfield(LEFT(Num, Index(Num, '.')), '.'), Num) AS Number;

LOAD * INLINE [

Num

237589

573830.098.9

98374.09

987464.1

84736.999.1

98383

6444.87

];