Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Creative Concatenation

HenrietteH
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'tend 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! Andit 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 aTranspose 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

Andthe data is ready for reporting!

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

Attachments
Comments
evejolivares
5 - Atom

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

RodLight
8 - Asteroid

@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

 

 

 

agcamig
5 - 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!

agcamig
5 - Atom

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