This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
- edited on
Let's take a look at this data set that needs to be combined into one field for reporting purposes:
but it shouldn't end up looking like this:
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?
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":
Then, using a Summarize tool, the remaining rows are concatenated together using a space as the delimiter:
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
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] + ".firstname.lastname@example.org"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 ""+IF [FvoFlag] = "Y" THEN "-FVO"ELSE ""ENDIF
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!