Number Formatting Issue

[#1]

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!

[#2]

I have tried using PurgeChar and KeepChar but it has not worked for me and only outputs one number that isn't even one of the numbers in the WR # field. I have managed to use in the script:

subfield([WR #],'R',1) as [WR ID],

keepchar([WR #],'R') as [Rework]

Using this I am able to both see all of the numbers in the WR # field (without letters), but I am able to create a table box like this:

WR #   Rework

28371     -

92941     R

19384     -

2921      R

Now the only problem now is that I cannot get other letters involved such as P, does anyone know how to incorporate more delimiters into this subfield function?

[#3]

Tab1:

LOAD * INLINE [

Srno,Number

1,2009

2,2015

3,2016P

4,2020R];

Tab2:

Load

PurgeChar(Number,'R''P') as Number1

Resident Tab1;

[swuehl]

could you post the script you have used trying the Purgechar() function?

And you are still coping with field values like posted in your sample Excel file?

[effinty2112 · Accepted solution]

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

[#6]

I did not use the purgechar() to get to where I am right now, my script looks like this as of now:

LOAD

  [WR #],

  subfield([WR #],'R',1) as [WR ID],

  keepchar([WR #],'R') as [Rework?]

From

;

edit: And yes I am still using the same field values like the sample excel file.

[#7]

Thank you this worked perfectly!!!!!!

[swuehl]

Glad that it is working now.

Still I would bet there must be an easier solution...

[#9]

This works for now, as long as additional letters do not get added as well. Thank you for your help!