Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Creative Concatenation

Alteryx
Alteryx
Created

 

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

 

2019-04-22_15-43-29.png

 

And the data is ready for reporting!  

 

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

Attachments
Comments

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

 

=CONCATENATE("A2",".s",(TEXT(B2,"00000")),".us@gmail.com")

 

Please help

Alteryx Certified Partner

@evejolivares,

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] + ".us@gmail.com"
ENDIF

 

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"
ELSE ""
ENDIF

 

 

 

Atom

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!

Atom

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