Filter or Remove Unwanted Letters or Characters Leaving Data in Row

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.

Screenshot 2024-09-19 121506.png

Screenshot 2024-09-19 121542.png

Workflow

Designer Cloud

Regex

Expression

Scheduler

Accepted answers

@sslattery17 use the replace funtion

Try a few formulas

Create a new field New Name

Replace([Primary Name], "(H)", "")

REGEX_Replace([New Name], "[^a-zA-Z0-9 ]", "")

Trim([New Name])

Check out all of the available Functions here: Functions (alteryx.com)

Screenshot 2024-09-19 144931.png

Chris

All comments

@sslattery17 use the replace funtion

Try a few formulas

Create a new field New Name

Replace([Primary Name], "(H)", "")

REGEX_Replace([New Name], "[^a-zA-Z0-9 ]", "")

Trim([New Name])

Check out all of the available Functions here: Functions (alteryx.com)

Screenshot 2024-09-19 144931.png

Chris

I used the following

replace([Primary Name],'(H)', "") and it removed the (H); I used same replace formula to try and remove the ** as shown below; but it removed all the names or replaced them with 0.

I used same replace formula to try and remove the ** as shown below; but it removed all the names or replaced them with 0.

replace({Primary Name], "**", "")

**Kimberly** Elam
Alexandria** Mickens
Kevin** Hanks

Also tried

and also regex_replace([Primary Name], "[^a-zA-Z0-9 ]", "") That left the ** and the (H).

The replace formula for the (H) worked but the regex formula did not work for removing the ** from any part of the name.

After cleaning up a few formulas in my workflow and moving a few around; both formulas as noted in this feed worked to remove/replaced the unwanted characters without filtering out the data. Thanks for the help.

Sample Data for review

The Sample Data.xlsx

@sslattery17 what is the expected result?

All sample files

S2Res_Workflow.zip

SR_RES_10102024.yxmd

Would this work flow be in addition to different schema macro? Can it be used in conjunction with the different sheet different schema macro. I have 71 sheets for this workflow.

I updated directory to location of all (76) sheets getting parse error. see attached.

Screenshot 2024-10-15 111953.png

Screenshot 2024-10-15 112329.png

@sslattery17 are you expecting the 71 sheets data to be combined into one output? Are these 71 sheets are from one workbook or different excel workbooks? also are these sheets are having different schema?

Hello @binu_acs. No on combining output, I load each property separate to audit in our system. They are separate sheets some have different schemas. Every client either sends different sheets though should be the same schema, are not; or they send one sheet again always have one or two different schemas. This client is different sheets & a few different schemas. I included the workflow my zip...all clients we are capturing & calculating the same sort of data.

your workflow is not properly uploaded, the macros are missing, you need to go to the menu options-exportworkflow, it will create a zip file which will include all the associated files with this workflow

I am not exactly following your requirement. I assume you will have separate excel file from different clients, my question is do you want to process each files seperately and save as different output based on the client?

@binu_acs Thank you. Find attached.

S2Res_Workflow.yxzp

@sslattery17 I am not exactly following your requirement. I assume you will have separate excel file from different clients, my question is do you want to process each files seperately and save as different output based on the client?

@sslattery17 can you also attach your macros, they are still missing

Please find attached the macros. Sorry about that; I guess I was not clear on your ask. I want a different output per sheet (property). My workflow is same client; each sheet is a different property for that one client.

Different File Schema Macro.yxmc

Dynamic Multi File Sheet Names.yxmc

@binu_acs @ChrisTX I am getting parse errors on creating formula for column Rent. Not sure why I am getting these errors. Can you assist or point me in the right direction to fix.?? The zip with the sample reports outcomes workflow I sent. a clients charge codes can be anything as you can see below; this prevents me from using just a ifcontains[charge code],"Rent" or "rent" as this picks up code that contains Rent or rent like Garage Rent or Parking rent....etc open to suggestions as long as it does not require a manual reference list.

if [Charge Code]="Rent" then [Amount] elseif [Charge Code]="rent" then [Amount] elseif [Charge Code]="Month to Month Fee" then [Amount] elseif [Charge Code]="Rent 3%" then [Amount] elseif [Charge Code]="Residential Rent" then [Amount] elseif [Charge Code]="Rent-" then [Amount] elseif [Charge Code]="Rent-PEP applied" then [Amount] elseif [Charge Code]="Rent PEP Applied" then [Amount] elseif [Charge Code]="Rent-minus PEP discount" then [Amount] elseif [Charge Code]="Corrected to best price quoted. Lease dates coincide with rate and LRO pricing" then [Amount] elseif [Charge Code]="Rent-Applied PEP" then [Amount] elseif [Charge Code]="Rent-3% PEP Applied" then [Amount] elseif [Charge Code]="Rent edited-incorrect amount" then [Amount] else 0 endif

Screenshot 2024-10-16 105122.png

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

@sslattery17 attaching the updated workflow

S2Res_WorkflowUpdated.yxzp

@binu_acs 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....)

@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

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.

Sample Client Data and Outcome.zip

@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?

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.

@ChrisTX @binu_acs

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"),"")

@sslattery17 @What is the expected result using this formula?

That is not the right format for a Regex_Replace function. you have an extra right parenthesis, but even if you fix that the function structure is not right.

Look at the examples on the Functions page.

REGEX_Replace(String, pattern, replace, icase): Allows replacement of text using regular expressions and returns the string resulting from the RegEx find (pattern) and replace (string). All occurrences of the match are replaced, not just the first.

The replace parameter can be either a specified value as shown below or a marked group, like "$1". Make sure that marked groups are wrapped in double quotes.

icase is an optional parameter. When specified, the case must match.

By default icase=1, which means ignore case.
If set to 0, the case must match.

Suggest you don't use a Regex here, you don't need it. Store all of those values in a Text Input tool with fields like 'Find this text' and 'Replace with'

Then use a Find Replace tool.

Chris

When I bring in all the property data; the first column [Bldg-Unit] has both the property name and the unit numbers. I have created a formula that works to copy the property names to a new property column. Now I am trying to remove the property names from [Bldg-Unit] leaving the unit numbers. Sometimes those unit numbers include letters for example 200A or 200-G not always just nemeric. I tried using the cleaning tool; I guess the replace or regex wont work....This is the same workflow I have the problem with the summarization issues. I have tried to resign the workflow, approach it differently and cant seem to get there. Been working on it for two weeks...

11th & Spruce
Oct 2024
[Null]
Unit Details
Bldg-Unit
201
[Null]
202
[Null]
203
[Null]
[Null]
[Null]
[Null]
[Null]
204
[Null]
[Null]
[Null]
[Null]
[Null]
205
[Null]
206
[Null]
[Null]
[Null]
[Null]
[Null]
[Null]
207
[Null]

@ChrisTX thanks for this. Its not working as expected because of other data I cant cleans

@sslattery17 just to trying to understand the requirement here, below given your sample data, what is the expected output you are expecting based on the below data

Quick Links

Unanswered
Community Events
Groups

Tags

Workflow
Output
Preparation
Datasets
Input
Developer
Help
Tips and Tricks
Common Use Cases
Server
Transformation
Developer Tools
Macros
Data Investigation
Best Practices

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703