Get all numbers after decimal

[MK9885]

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

[eddie_wagt]

Hello,

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

Regards

Eddie

[MK9885]

It's a dimension.

[MK9885]

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.  

[eddie_wagt]

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 · Accepted solution]

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
;

[MK9885]

Didn't though about that..

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

Thanks