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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Is there an easy way to concatenate all combinations of non-null columns to 1 string?

AkisM
10 - Fireball

I have about 20 different columns, each one with a unique comment describing the record in that row (They are different data validation checks). To make it easier for the reviewer to see, I would like to go from 20 to 1. When there is a value in one of those columns, add them to the master column "Comment". Ignore OK's. Example below:

 

ValueCheck1Check2Check3Check4Check5Check6
40OKOKbad formatcheck duplicationcheck formulaOK

 

would like it to be

 

ValueComment
40bad format and check duplication and check formula

 

The "before" table could have any number of different combinations of checks that are OK and checks that aren't ok and should be included in the comment.

 

Obviously with a larger number of checks the number of combinations of possible OK's or not OK is too big to code in a formula... Is there an easy way to achieve this?

4 REPLIES 4
BenMoss
ACE Emeritus
ACE Emeritus

Hi @AkisM 

 

This process is easily achievable with the transpose tool, filter tool and summerize tool.

 

I've attached an example for you.

 

BenMoss_0-1580388987329.png

 

Ben

AkisM
10 - Fireball

@BenMoss Thanks, is there a way to separate the concatenations with something like "and" or a comma to make it more legible? Keep in mind they're not always simple 2-word sentences.

BenMoss
ACE Emeritus
ACE Emeritus

Yes in the summerize tool, when you select the field you are concatenating in the bottom panel you are able to specify the first, middle and last delimiters.

 

Ben

jacob_kahn
12 - Quasar

I think that @BenMoss 's response is the best option for you.

You can also use a transpose and cross tab tool. Embedded in the cross tab tool is the option to specify the delimiter. 

 

jacobkahn_1-1580390466296.png

 

Labels