topic Re: Loading in number string without preceeding characters in QlikView

Loading in number string without preceeding characters

Mon, 09 May 2016 15:02:12 GMT

Hi I'd like to know how to load in a series of numbers from a field without the proceeding characters. For instance I have

Sales :

$35666

£50000

(6788

)89998

I want to return the list so that it only loads in the numbers ie :

35666

50000

6788

89998

Re: Loading in number string without preceeding characters

marcus_sommer — Mon, 09 May 2016 15:03:32 GMT

You could use: keepchar(Sales, '0123456789') as Sales.

- Marcus

Re: Loading in number string without preceeding characters

sunny_talwar — Mon, 09 May 2016 15:06:15 GMT

Or this:

Right(Sales, Len(Sales) - 1)) as Sales

Re: Loading in number string without preceeding characters

Mon, 09 May 2016 15:20:16 GMT

Thanks - would this work correctly even if one the values didnt contain any characters or will always remove the first character ?

Re: Loading in number string without preceeding characters

marcus_sommer — Mon, 09 May 2016 15:27:32 GMT

It will always remove the first character. The above mentioned keepchar-function will with these parameter only return numeric chars. If your data are more different then that then you should post more of them.

- Marcus

Re: Loading in number string without preceeding characters

sunny_talwar — Mon, 09 May 2016 15:28:16 GMT

This will always remove the first character.

You can do this if you don't want to remove in case the first character is a number

If(IsNum(Left(Sales, 1)), Sales, Right(Sales, Len(Sales) - 1))) as Sales