Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

biester
Valued Contributor

Question about usage of num#

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

1 Solution

Accepted Solutions
Not applicable

Re: Question about usage of num#

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

7 Replies
michielvandegoo
Valued Contributor

Re: Question about usage of num#

Try this:

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

I tried num# but it wasn't succesfull.

Regards

Not applicable

Question about usage of num#

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

Purgechar(MyField,'_') as MyField.

biester
Valued Contributor

Question about usage of num#

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.

Not applicable

Question about usage of num#

hi,

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

regards

biester
Valued Contributor

Question about usage of num#

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
Not applicable

Re: Question about usage of num#

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

biester
Valued Contributor

Re: Question about usage of num#

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

Thx & best regards

Community Browser