Ability to concatenate within a column via bionic rules

Hi Andrew, Assuming you have a finite list of potential  vales in the Name that you are working with (or whatever the equivalent field is) here is a way you can do it using a Pivot action.  Start with a Dataset task that pulls in the data as in above.  Next create a Pivot task with "Project ID" in the Show and the Group By. 
In the Pivot section drag in the Name field and then click on the gear icon to get the "Pivoted conditions to evaluate" screen.  You will add one line in this config for every field value in Name.  For example, you would have Pivoted Field = Name, Operator "=", Value "Telephone"   Aggregation "MAX" (won't actually do anything), Field is "Name" with no Default Value and then Field Label "Telephone".   You would then do similar things for Internet, Television and any other values that you care about. 
What you end up with is something like this

Project ID || Telephone || Internet || Television
123           || Telephone || Internet || 
345           ||                   || Internet ||
456           || Telephone ||               || Television

You can then create a final Transformation task that uses the Concat function to replace the pivoted columns with one column that concatenates the string values.  (You can add spaces or commas, too, but the UX is a bit tricky.) 

I hope this helps!

it is not a finite number. It can be 5 or 10 or 20 assets

Andrew, I think this might work for you anyways. If the result of the Pivot is null, then that value simply doesn't get included in the concat.

The way I'm doing it is:

1. Use a Formula Field in a Transform Task to concatenate each "Name" with " // ". So, in your example, each record for "Telephone" becomes "Telephone // " So, when they're concatenated together, it'll look like "Item 1 // Item 2 // ".  (Not perfect, but not terrible.)
2. Do the pivoting that Karl describes above. (Make sure to use MAX, as he recommends. I first used "COUNT", which produces a numerical output, which can't be included in a concatenation.) I have 7 possible options, so my setup of the Pivot field looks like this: [img]https://uploads-us-west-2.insided.com/gainsight-us/attachment/RackMultipart20180307-104427-1btq1f8-Image_2018-03-07_at_3-46-32_PM_inline.png[/img]
3. Use a Transform Task to concatenate all of them together: [img]https://uploads-us-west-2.insided.com/gainsight-us/attachment/RackMultipart20180307-49572-1upa433-Image_2018-03-07_at_3-49-18_PM_inline.png[/img]

I could see a dynamic "concatenate contact column grouped by a value" being valuable in the future. That way if there is an additional new string, one wouldn't have to go back to the pivot and add it.

yes, but I do not have only 7 possible options. I never know how many options I have. so I have no idea how many times I would need to concatenate. 

I agree with you, Kevin.  I think what would really work for Andrew's use case is a new aggregation option when grouping by a value -- instead of Count or Count Distinct he could use a Concat.   
Are there other aggregation options that would be useful in different use cases (and with different data types)?

So we are saying the functionality is not currently available and that the above suggestions with the current functionality will not work for my use case right? I just want to make sure I have not missed something or not understanding something. 

I am not able to think of a way to do this when there are no bounds on the potential column values and you don't want to group them into a finite set.  (Not saying that someone else might not be able to come up with an approach!) The group-by aggregation enhancement would fit the use case pretty cleanly. 

Hi Andrew -- I think what I picked up from your description is that you [i]do have a finite list of potential column values, but you just don't know how [i]many of them will be present for a given record. If that's the case, then that matches my situation, so my approach should work for you. That may make you read my description above thoroughly for the first time 🙂 So let me know if there's anything I can clarify!

Thanks Seth. I do not have a finite list. there are so many combinations things....exponential variables per say. 

I guess the point I want to be clear about is that [i]combinations of things aren't a blocker. If you have 20 possible options, that could create a gazillion possible combinations, but there are still just 20 underlying options.

Haha! yes, you are correct. Good option just not plausible for what I need to do 🙂

Ok great, I'm happy that you understand and sad that you're still up a creek 🙂

I am looking for exactly this.

We want to be able to concatenate account names (which will then be a token) for Advanced Outreaches to individual emails.

My issue is that I have a variable number of account names (generally increases every day) and varying amount of email addresses. Emails can be assigned to any number of accounts, but I only want to email that person once, with a list of all accounts they're associated with. If there's an elegant solution and I'm missing it, I'd be happy to hear it, but I've spent quite a bit of time searching for one, and I don't believe there is yet. Glad to hear other people have already presented the idea.

Thanks for the post, Brad.  This is a very strong use case for the concept discussed earlier in the thread -- Concatenation as an aggregation option when grouping in a query.  The team is looking into this.  I unfortunately don't have a terrific work around for you (though with folks like Andrew and Seth on the thread I wouldn't be surprised if someone else had more ideas than me!) but here is one thought just in case it could fit your scenario.  Let me suppose that you have already solved the problem up to some point but need to add in some new accounts.  In other words, you have a table that looks like  EMAIL ADDRESS || Union of All ACCTS for that EMAIL.   However, you now get a bunch of new customers and you need to add them in.   So you have a second table with EMAIL ADDRESS || NEW Account.   (For simplicity, I am going to assume that in the second table each email can show up at most once.)   If your problem can be structured like this (maybe or maybe not) then you can solve it by doing an outer join (merge) of the current state table and the new table on the Email Address column and then using the row level Concat function.  

WOAH HOLD ON MIND EXPLODING

Using a Rule to recursively concatenate values onto an existing list??

So, each time you run the Rule, if there's an Account that's not already present in the list for a certain email address, the Rule concatenates the existing list of Accounts with the new Account.

If multiple Accounts are being added to the list, the Rule may take several runs to add all the items. It just doesn't work if you need to [i]remove items from a list.

Karl, am I just repeating what you had in mind, or am I going off on a different tangent (that may or may not actually work given the devil in the details)?

Yes, that is what I have in mind.  And you are right, as stated it doesn't work for removing.  If you had a way to easily split the original data set into subsets where at most one account per email address was included, you could make this work as a general solution.  For example, suppose we had a date column and each email address would never show up twice with the same quarter.  Then you could fetch one data set for each quarter and then just join them all on the email field and concatenate.  In theory you could do this even without the extra field by pulling off the 'first' row for each email address (by some sort), subtracting it from the base data set, and then repeating up to the max number of accounts with the same email address.  However, this would be VERY PAINFUL so I don't really recommend this direction unless your data naturally makes splitting the data out more straightforward. 

So do everything as described but throw in the date field on the second table to be updated (upsert) to current date when you run the rule to populate that. So if it finds a record then the date will be updated but if it does not find a record then the date will be old. Then create another column on the second table "Active" and run a rule to populate that field with a "yes" if date = current date and "no" if date is older than rule date. Then run your rule to concatenate by rows and throw in the filter of Active = "Yes" and build your reporting off the concatenated data set. 

 Here is basically what I'm trying to do:

What do you want the concatenation to look like?