community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Join from two different Inputs

Asteroid

hi everbody,

 

i'm an alteryx newby and have a problem to generate a succesfull join between two inputs because some strings from the column "Dealnamen" from the left input are not written the same way as they are written in the right input (shown in screenshot_1). i'm making the join to get an additional column from the left input. how can i fix it in alteryx? do i have to replace just a part of the strings which are different? or can i make an if-clause, so if it' written "xxx" then it should be wirtten "yyy" (replace the full string of the left part with the string of the right part?

 

Otherwise the join works between the left and right Input.Thanks so much for your help! Best regards

 

screenshot_1.JPGscreenshot_2.JPG

I would suggest using a Text to Columns tool to split the Dealname into 6 columns on both sides

 

It looks like 5 of these would then join with no problem.

 

You can then use a formula tool to adjust the left input to match the right (e.g. by removing the extra dash).

Asteroid

Hey,

 

i have made it with this Statement for one example: REGEX_Replace([Dealnamen], "PREX – alle Kunden", "PREX alle Kunden")

 

Generally, how can i include more statements into this formula? Because there are more words with the sign "" which i want to replace. The Statement REGEX_Replace([Dealnamen], "PREX – alle Kunden", "PREX alle Kunden") OR REGEX_Replace([Dealnamen], "VIVAKI – alle Kunden", "VIVAKI alle Kunden") does not work. So i just want to have more conditions in this Statement.

 

Thanks!

Alteryx Partner

REGEX_REPLACE only does one replace, but it would be better if you made it more dynamic, like this ... 

 

REGEX_Replace([Dealnamen], " - ", " ")

that would replace every occurence of <space><dash><space> with a single <space>   

 

This still isn't using regex syntax, but it's a way to make it work for you :) 

I would suggest just a plain old replace:

Replace([Dealnamen], " - ", " ")
Nebula
Nebula

hi @Marek89

 

@cmcclellan  and @jdunkerley79 replace solutions should work, but from the image you provided, it looks like your initial [Dealnamen] contains an Em-Dash which is not the same as the standard dash. It's longer than the other dashes in that field.  Your syntax should be as below

 

Replace([Dealnamen], " – ", " ")

The best way to be sure is to copy the dash from your source file "PREX – alle Kunden" and paste it into the alteryx formula, replacing the dash that's already there

 

Dan  

Asteroid

Hey all, perfect it works!

 

What is the difference between "Replace" and "REGEX_Replace"?

 

So can i just use one expression in a "replace condition" or general in a condition? Generally, is it not possible to include more replacement conditions in one replacement expression? If i want to Replace  " – " with " " and additional for example "+" with " ", how should the expression look like? Or do i have to use the formula tool two times in a row? 


Or an other example: if i'm using a filter tool with this Expression: !Contains([Dealnamen],"Open Auction") AND !Contains([Dealnamen],"OA") -> is it possible to include it into one Expression. Because in my example i Need two times the Expression !Contains([Dealnamen]

 

Thank you so much for your great help!

Replace looks for a specific string whereas REGEX_Replace allows for Regular Expressions.

 

To do in a single expression you could do:

REPLACE(REPLACE([Dealnamen],"  ", " "), "+", " ")

alternatively in REGEX:

REGEX_REPLACE([Dealnamen],"  |+", " ")

 

Again for the contains you could use REGEX:

!REGEX_Match([Dealnamen], ".*(Open Auction|OA).*")

Another option is to use the Find and Replace tool to add a column which you can then filter on. This tool could also be used to do the repeated replacements needed.

Asteroid

Hey,

 

First question: for what is your last expression: !REGEX_Match([Dealnamen], ".*(Open Auction|OA).*") 

 

If i am doing this, my output in the column "Dealname" is "-1" or "0" instead of the dealnames.

 

Second question: I just want to say, that i do not need the dealnames, where the a part of the string includes "Open Auction" or "| OA |". Which Expression can i use instead of using !Contains([Dealnamen],"Open Auction") AND !Contains([Dealnamen],"| OA |")?

 

Thank you :)

Labels