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.
If [WR#] has no more than one letter in it then this might work for you
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#],
[Estimated Project Hours],
[Actual Hours Spent]
[Sample WR sheet.xlsx]
(ooxml, embedded labels, table is Sheet1);
very odd that you find Purgechar() isn't working for you.
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?
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'.
Load Num#([WR #], '#####','R') as ID,
LEFT JOIN Number
Load [Request Number] as ID,
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.
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 #]?