Question about usage of num#

[biester]

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

[Michiel_QV_Fan]

Try this:

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

I tried num# but it wasn't succesfull.

Regards

[#3]

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]

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.

[#5]

hi,

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

regards

[biester]

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

[#7 · Accepted solution]

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]

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

Thx & best regards