Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master
Master

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
sunny_talwar

What if you just use this:

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

sunny_talwar

Alternatively, try this:

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

Not applicable
Author

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

sunny_talwar

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
Author

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 ...

rwunderlich
MVP & Luminary
MVP & Luminary

Try this in the script:

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

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

swuehl
MVP
MVP

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
Author

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

swuehl
MVP
MVP

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 #]?