Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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
swuehl
MVP
MVP

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
Not applicable
Author

Thank you Stefan

sunny_talwar

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
Champion III
Champion III

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

];