topic Re: Find first letter in values in QlikView

Find first letter in values

Anonymous — Thu, 16 Jan 2014 15:51:32 GMT

Hi, my datasource has one column where the values look like this:

1 061,020 M3

2 645,016 MWH

The field value contains both numeric and text data, and I need to get rid of the unit on the right side of the field. I want the result to be:

1061,020

2645,016

The problem is that the unit can be like the example above ' M3', which also contains a numeric value. Since I always have three decimals I tried to combine floor() and purgechar(ABC.....) to get rid of the letters and round down to three decimals, but unfortunately that failed. The best way, as I see it, would be to find the first letter and then skip everything from there but I don´t know if thats possible. Any suggestions?

MVH

Johan

Re: Find first letter in values

nilesh_gangurde — Thu, 16 Jan 2014 15:54:05 GMT

You can use the Left Function to achieve the same.

left( s , n )

Substring of the string s. The result is a string consisting of the first n characters of s.

Example:

left('abcdef',3) returns 'abc'.

left(Date, 4) where Date = 1997-07-14 returns 1997.

-Nilesh

Re: Find first letter in values

Anonymous — Thu, 16 Jan 2014 15:55:20 GMT

Johan,

Is there always a space between the groups? If yes, you can use this:

subfield(<your field>, ' ', 1)

Regards,

Michael

Edit: didn't notice there were two spaces. Fixing:

subfield(<your field>, ' ', 1) & subfield(<your field>, ' ', 2)

Re: Find first letter in values

hic — Thu, 16 Jan 2014 15:58:26 GMT

You could use

Left(Field, Index(Field, Left(Keepchar(Field,'ABC...'),1))-1)

I.e. the ABC... should list all characters. The Left function returns the first character and the Index function returns its position.

HIC

Re: Find first letter in values

Anonymous — Fri, 17 Jan 2014 09:36:34 GMT

Works perfekt, thanks