Filter or Remove Unwanted Letters or Characters Leaving Data in Row

[sslattery17]

My input source has a column with primary name and/or residents.  The client sometimes uses designations (H) or (H) Shannon**Slattery** to identify the tenants unit type etc...I have tried to filter this out a few different ways including using regex_replace([Residents],"[^\sa-zA-Z]",'') or regex_replace ([Residents],"(H)",'')  I need the tenant name to stay but any and all designations to be removed.  I even tried the find replace and that did not work as expected.  I have attached screenshots.  With 6 weeks into the use of this tool; any guidance suggestions, would greatly be appreciated.  

  

[ChrisTX]

In your Formula tool, your output data type is V_WString.  Guessing it should be a Fixed Decimal?  Maybe use the same data type as [Amount].

The error is because in the final ELSE statement you are assigning a value 0, but the data type is not numeric.

Instead of hard-coding all of those "Client text strings that should be considered RENT", suggest you maintain all of those values in an Excel file.  Then read in the Excel, use a JOIN tool, and run the Left Output Join anchor through some validations to ensure you don't have any new values that need to be added to the Excel file.  A validation could be something like "if the amount is over 500, determine if the description represents RENT".

Chris

[sslattery17]

Hello @ChrisTX Thank you.  Okay I will take a look at the string values.  I want to prevent using and maintaining a manual excel sheet for codes; this particular client has over 2000K across the 76 properties.  I appreciate the added approach.  Ill see what I can figure out.

[binuacs]

@sslattery17 updated formula attached

IF [Charge Code] IN ("Rent", "rent", "Month to Month Fee", "Rent 3%", "Residential Rent", 
                     "Rent-", "Rent-PEP applied", "Rent PEP Applied", "Rent-minus PEP discount", 
                     "Corrected to best price quoted. Lease dates coincide with rate and LRO pricing", 
                     "Rent-Applied PEP", "Rent-3% PEP Applied", "Rent edited-incorrect amount") 
THEN [Amount] 
ELSE 0 
ENDIF

[binuacs]

@sslattery17 attaching the updated workflow

[sslattery17]

@binuacs how would i capture all the rent codes?  The workflow looks like it is capturing total as rent or only the "Rent" code.  This also does not allow me to capture the other codes in their own column.  The final results would be a column that totals each of the charge codes by tenant by property.  (Rent, Pet Rent, Storage, Garage, Pest, Trash, etc....) 

[binuacs]

@sslattery17 This workflow just converting the given file to a proper format based on the given input file, it doesn’t do any data manipulation, if you are expecting any other changes, upload a new file with excepted results 

[sslattery17]

In this thread are all the rent codes we capture and sum.  the results has a column for each of the other codes I mentioned.  I thought maybe using rows to columns but wasnt sure that was the correct solution either.....Thanks for all the help.

[binuacs]

@sslattery17 Can you tell me for the below record are you expecting a new column to be in the output file (second file) with the total 1450.00?

[sslattery17]

Yes that is what I am expecting returned for that tenant.  If she had pet rent or carport or garage then that would also be returned.

[sslattery17]

@ChrisTX @binuacs 

I have the following formula and I cant get it to work.  Getting a parse error, can you help identify where my problem is?

regex_replace([Bldg-Unit ], "11th & Spruce",
"250 High",
"600 Goodale",
"80 On The Commons",
"801 Polaris",
"Baxter Park",
"Bracken House",
"Buckstone Flats",
"Burrough's Mill",
"Devonshire Apartments",
"Dixon House",
"Edison at Gordon Square",
"Emerson Park",
"Gateway Lakes Apartments",
"Gateway Lofts - Centerville",
"Gateway Lofts - Columbus",
"Gateway Lofts - Lansing",
"Gateway Lofts Lexington",
"Graham Park at The Highlands",
"Harper House at The Highlands",
"Heritage Green",
"Lane Lofts",
"Lofts at Norton Crossing",
"Lumina",
"Luxe 88",
"Luxe at The Highlands",
"Madison House",
"Marina Lofts",
"Marine Club",
"Market and Main",
"Minnetonka Station",
"Mulberry Lofts",
"North Shore Flats",
"Packard Building",
"Park's Edge at Shelby Farms",
"Pennbrook Station",
"Polo Run",
"Pulliam Square",
"Rize at Opus Park",
"Spectra",
"Spring House at Brandywine",
"Station 324",
"Steel House",
"Stillwell at Avery Centre",
"Stillwell at Jerome Village",
"Stilwell at Wellen Park",
"Sugar Run at New Albany",
"The Aubrey",
"The Baylor",
"The Darby at Briarcliff",
"The Flats at Douglas",
"The Gramercy - New Albany",
"The Jacqueline",
"The Kingston",
"The Monarch",
"The Normandy",
"The Preserve at Forest Creek",
"The Reserve of Sugarcreek",
"The Stratton",
"The Thomas",
"The View on Grant",
"Tribeca",
"Willis Avondale Estates"),"")