Solved: Re: Number Formatting Issue - Qlik Community

Report Inappropriate Content · ‎2016-06-14

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!

effinty2112 · ‎2016-06-16

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

sunny_talwar · ‎2016-06-14

What if you just use this:

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

sunny_talwar · ‎2016-06-14

Alternatively, try this:

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

Report Inappropriate Content · ‎2016-06-14

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

sunny_talwar · ‎2016-06-14

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?

Report Inappropriate Content · ‎2016-06-14

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 · ‎2016-06-14

Try this in the script:

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

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

swuehl · ‎2016-06-14

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.

Report Inappropriate Content · ‎2016-06-15

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 · ‎2016-06-15

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