Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Number Formatting Issue

Hi,

I am currently working on an excel sheet comparing numbers that are assigned to different field values in order to create logical data. The numbers are typically formatted as ex. 2938 or 20098. There are a couple of numbers that contain a R or a P at the end indicating that they are in rework or if they are a project. In the script I have tried using Num(Num#([WR #], '#####','R')) in the script. [WR #] is the field that contains those numbers. Without this, I cannot see a number like 20920R or 1097P. Using the Num function, I am able to at least see the number itself, but it does not contain the R or P, which is critical to my data. If anyone knows how to fix this, it would be very helpful.

Thanks!

1 Solution

Accepted Solutions
effinty2112
Honored Contributor

Re: Number Formatting Issue

Dear Caitlin,

If [WR#] has no more than one letter in it then this might work for you

Data:

LOAD

  [WR #],

  Subfield(Replace([WR #],keepchar([WR #],'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),'|') & keepchar([WR #],'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),'|',1) as [Numeric WR#],

  Subfield(Replace([WR #],keepchar([WR #],'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),'|') & keepchar([WR #],'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),'|',2) as [Alpha WR#],

     Developer,

     [Date Assigned],

     [Estimated Project Hours],

     [Actual Hours Spent]

FROM

[Sample WR sheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

very odd that you find Purgechar() isn't working for you.

Good luck

Andrew

View solution in original post

27 Replies

Re: Number Formatting Issue

What if you just use this:

Num#([WR #], '#####','R')

Re: Number Formatting Issue

Alternatively, try this:

Alt(Num#([WR #], '#####'), Num#([WR #], '#####R'), Num#([WR #], '#####P'))

Not applicable

Re: Number Formatting Issue

The number is displaying, but the letter is still not appearing.

Re: Number Formatting Issue

If you are looking this in a table box object, can you make sure that you have not set the number format to be numeric? Can you change it to expression default and see if that works?

Not applicable

Re: Number Formatting Issue

It is not set to numeric format, it is just in its default format. If this number is the link between multiple tables inside the script, would I have to put the num function in front of all of them? For example, I have a table called [Number] and a table called [Request] in the same tab that are connected by the field 'ID'.

QUALIFY *;

UNQUALIFY [ID];

Number:

Load Num#([WR #], '#####','R') as ID,

...

From ...

QUALIFY *;

UNQUALIFY [ID];

Request:

LEFT JOIN Number

Load [Request Number] as ID,

...

From ...

MVP & Luminary
MVP & Luminary

Re: Number Formatting Issue

Try this in the script:

Dual([WR #], keepchar([WR #],'0123456789')) as [WR #]

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

MVP
MVP

Re: Number Formatting Issue

There might be an issue if you are having field values like 2938 and 2938P and you expect them to link on  (or be a text representation of) the same numeric value, 2938.


For any given numeric value, QV will store at most a single text representation, the one the comes first in load order, I believe.


Not applicable

Re: Number Formatting Issue

Still not getting the letter to show. I've even tried putting it on every [WR #] field and it won't work properly either.

MVP
MVP

Re: Number Formatting Issue

Would you be able to create a small sample QVW  (could contain mock up /scrambled data for all columns except [WR #]) and post it to this thread?

edit: well, this probably won't show the original data, so maybe it's better if you could provide sample input files for [WR #]?