Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Creative Concatenation



Let's take a look at this data set that needs to be combined into one field for reporting purposes:


11-11-2016 4-58-03 PM.png


but it shouldn't end up looking like this: 


11-11-2016 5-00-02 PM.png


That is no better for reporting purposes than the data we started with because we only want to see areas that need improvement and we don't want to just combine values separated by commas. 


So how can we get it to look like this?


11-11-2016 5-02-40 PM.png


A formula such as this one could work: 

If contains([ProdDessert], "Improve") then [ProdDessert] else "" endif + " & " +
If contains([ProdSupplies], "Improve") then [ProdSupplies] else "" endif + " & " +
If contains([ProdFood], "Improve") then [ProdFood]else "" endif + " & " +
If contains([ProdFoodSvc], "Improve") then [ProdFoodSvc] else "" endif


But that's a lot of typing! And it will require cleaning up of "&" and spaces where they aren't needed. 


So let's look at a dynamic way of doing this: 


First, using a Transpose tool, the columns are turned into rows and the data is filtered for rows that contain the word "Improve": 


11-11-2016 5-16-53 PM.png


Then, using a Summarize tool, the remaining rows are concatenated together using a space as the delimiter:


11-11-2016 5-17-37 PM.png


Lastly, some cleanup is done in a Formula tool




And the data is ready for reporting!  


Also, take a look at the attached workflow that walks through these steps. 


I am trying to Concatenate 2 rows into one to create an email address. This is what i used to use in Excel 




Please help

Alteryx Certified Partner


Unfortunately since you commented on an article and didn't post this as a Discussion question, I can't attach an example workflow to show this.

But assuming that in your data you have something that you could use to group the related records (and if not, assuming it is just every other record that starts a "new" email, you could create a grouping within Alteryx), you can do this with a Multi-Row Formula tool. 


You would "Create New Field", set "Values for Rows that don't Exist" to NULL, Group By the column that indicates the grouping of related email "parts", and then use an expression like...


IF IsNull([Row-1:Email Parts]) THEN NULL()
ELSE [Row-1:Email Parts] + ".s" + [Email Parts] + ""


It will create a NULL value in the first record for the New Field and put the Full Email address in the second record, so then you can just filter on the New Field for NULLs.

My syntax  for concatenation is, what needs to be corrected?


[LN_ID] + "-" + [GL_MATRIX] + "-" +
IF [HfsFlag]="Y"
then "HFS"
elseif [HfsFlag]="N"
then "HFI" else ""
+Misinformed If.PNG
IF [FvoFlag] = "Y" THEN "-FVO"





hello, my simple concatenation  in Formula tool is: 


 [Text]+ "-" +[Text2]


but I keep getting error in RED: "The field "" is not contained in the record. (Expression #1)"


There are no null values under 'Text' and 'Text2'


Would appreciate any advise. Thanks!


Apologies, please ignore previous comment - error was generated since I was not able to define new field header generated. Thank you!