topic Removing decimal in QlikView

Removing decimal

Tue, 13 Sep 2016 15:10:05 GMT

I want to take return all the digits before the FIRST decimal in a list.

I have

1920.1008.678

1800.786.894

So should return :

1920

1800

Thanks

Re: Removing decimal

kaushiknsolanki — Tue, 13 Sep 2016 15:12:37 GMT

Hi,

You can try mid function as below.

=Mid('1920.1008.678',1,Index('1920.1008.678','.',1)-1)

Regards,

Kaushik Solanki

Re: Removing decimal

sunny_talwar — Tue, 13 Sep 2016 15:14:40 GMT

Or may be this:

SubField(FieldName, '.', 1)

Re: Removing decimal

Tue, 13 Sep 2016 15:15:42 GMT

I have a long list of such numbers not just the two examples mentioned. Also I have some with just one decimal such as

1820.898

Re: Removing decimal

kaushiknsolanki — Tue, 13 Sep 2016 15:18:21 GMT

Hi,

The Number is just an example, you will have field name instead of the value in your script.

So your expression will be

Load *,Mid(FieldName,1,Index(FieldName,'.',1)-1) as Number From xyx;

Or as Sunny said it will be

Load *,Subfield(FieldName,'.',1) as Number From xyx;

Regards,

Kaushik Solanki

Re: Removing decimal

Tue, 13 Sep 2016 16:24:24 GMT

Thanks both.

Sunny I tried your solution and whilst it does work, if there are two separate rows with the same value it only returns for one of them.

So if I have

1880.889.190

it only returns 1880 for the first row and blank for the second

Re: Removing decimal

sunny_talwar — Tue, 13 Sep 2016 16:26:34 GMT

It must be returning both values, but just not showing it. Add a new field called RowNo() as Key and use that in your table box to see both the rows individually.

Re: Removing decimal

miguelbraga — Tue, 13 Sep 2016 16:51:25 GMT

Try simply this:

left(Value, 4)

Should surfice you

Regrads,

Re: Removing decimal

MarcoWedel — Tue, 13 Sep 2016 21:23:58 GMT

just for fun another one:

=TextBetween('\'&YourField,'\','.')