Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MK9885
Master II
Master II

Get all numbers after decimal

Hi,

I have a column with numbers

1234567

1234567.1

1234567.10

987654.2

987654.20
Qlik is ignoring 0 after decimal point. Which is normal. But I want both

1234567.1 & 1234567.10 etc...

I want to bring in anything after decimal without concatenating the numbers

 

Labels (2)
1 Solution

Accepted Solutions
eddie_wagt
Partner
Partner

Hello @MK9885 ,

On the first load Qlik already reads it as numeric as the source probably tells Qlik to do. You have to solve this in your SQL statement like this.

 

LOAD   Text(Number) as Number
,      OTHERFIELD
;
SELECT CAST(Field AS varchar) as Number
,      OTHERFIELD
FROM   SCHEME.SOURCE
;

 

View solution in original post

6 Replies
eddie_wagt
Partner
Partner

Hello,

Do you want these numbers as a dimension or will you use them in a expression. Display versus calculation?

Regards

Eddie

MK9885
Master II
Master II
Author

It's a dimension.

MK9885
Master II
Master II
Author

As shown below in SQL DB table,

4667547.2

4667547.20

But when I load this in Qlik, it concatenates both numbers & just giving me 4667547.2.

they are 2 separate records.  

2021-05-21_090343.jpg

eddie_wagt
Partner
Partner

You can load this field as text if you want to use it as a dimension. Qlik automatically sees this field as numeric, so you have to explicitly tell it is text. 

LOAD text([column_with_number]) as column_with_number
,    [other column]
,    [etcetera]
FROM [yadiyada.qvd] (qvd);

 

eddie_wagt
Partner
Partner

Hello @MK9885 ,

On the first load Qlik already reads it as numeric as the source probably tells Qlik to do. You have to solve this in your SQL statement like this.

 

LOAD   Text(Number) as Number
,      OTHERFIELD
;
SELECT CAST(Field AS varchar) as Number
,      OTHERFIELD
FROM   SCHEME.SOURCE
;

 

View solution in original post

MK9885
Master II
Master II
Author

Didn't though about that..

Thanks. It worked at extract level. Loading in final app to see the results.

Thanks