topic Re: Question about usage of num# in QlikView

Question about usage of num#

biester — Thu, 29 Sep 2011 11:15:21 GMT

Perhaps a simple problem, nevertheless I diddn't succeed in solving it:

In my database I have a string field with this format: 1234_.000 (numbers followed by an underscore and a dot and then three zeroes).
In QlikView I want to make a numeric field out of it: 1234.

Is this possible by ONLY using num# ? I know I could simply use num(replace(myField,'_.000','')) as my_NumField, but I thought it should actually be possible by only using num# .

I'd be thankful for any suggestions.

Rgds,
Biester

Re: Question about usage of num#

Michiel_QV_Fan — Thu, 29 Sep 2011 11:30:24 GMT

Try this:

subfield(myField, '_', 1) as ....

I tried num# but it wasn't succesfull.

Regards

Question about usage of num#

Thu, 29 Sep 2011 11:39:13 GMT

If you get rid of the character values from within the field, QlikView will automatically recognize it as a numeric.

Purgechar(MyField,'_') as MyField.

Question about usage of num#

biester — Thu, 29 Sep 2011 11:56:48 GMT

Thanks a lot for your suggestions. I know the problem can be solved using purgechar etc., but as said I wonder if there is a way to do it by ONLY using num#. Actually the manual uses the example num#(A,'abc#,#') which is obviously able to mask out the leading string 'abc'. So I imagined a num#(myField,'#_.#' ..) might do the job. I tried several variations, but without any success.

Question about usage of num#

Sun, 09 Oct 2011 07:48:09 GMT

hi,

this can be done with num#(myField,'#_.000', '.', ',')

regards

Question about usage of num#

biester — Thu, 03 May 2012 07:49:38 GMT

Sorry for late answer. I tried with num#(myField,'#_.000', '.', ','), but it didn't work either. The figures look thus:

num#(QUARK,'#_.000','.',',')
768_.000
1234_.000
76514_.000

Re: Question about usage of num#

Thu, 03 May 2012 08:03:25 GMT

Hi,

here is example, num_val values contain numbers without _.000 parts:

str_table:

LOAD * INLINE [

str_val

1234_.000

5555_.000

77.7_.000

1_.000

1111111111111_.000

];

num_table:

LOAD Num(Num#(str_val, '#_.000', '.', ',')) as num_val

Resident str_table;

regards

Re: Question about usage of num#

biester — Thu, 03 May 2012 08:32:04 GMT

OK, of course, I forgot NUM(), so actually Num(Num#(str_val, '#_.000', '.', ',')) solves the problem.

Thx & best regards