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

Turning a vertical list of numbers in Excel into a string -with commas separating each one

luciebrett
8 - Asteroid

Hello everyone,

 

I have a vertical list of numbers in Excel and I would like to put these numbers into a string for use in SQL server. In the image below, I have included the formula I could use to add commas. I would often just do this manually but I thought that it would probably be easy with a multi-row formula but I couldn't get this to work myself. Does anyone have an idea of how I should go about using Alteryx to do this automatically?

 

Thanks so much, Lucie


list of numbers.PNG

7 REPLIES 7
vishwa_0308
11 - Bolide

Hi @luciebrett

 

You can do this in alteryx, please refer the attached screenshot.

 

First convert the numbers using select tool to string.

Multirow.jpg

vishwa_0308
11 - Bolide

Hi @luciebrett,

 

Earlier i thought you want to add current row and row+1 but hope you want all your rows into a column separated by "," Then in this case you can use crosstab tool.

1. Convert your Field(Number) into a string type.

2. create one field(Group) with same value ( To grouping all values) or use if you have one in your data.

 Please refer the screen below:

3. Default separator is "," itself.

Crosstab.jpg

 

Kenda
16 - Nebula
16 - Nebula

@luciebrett As long as your field is stored as a string field, you could also easily just add a Summarize tool and choose Concatenate.

jdunkerley79
ACE Emeritus
ACE Emeritus

I would use a select tool to convert to a string then summarise to concatenate

2017-11-03_11-46-28.jpg

 

Sample attached

luciebrett
8 - Asteroid

Thanks @Kenda, super quick and easy! I have never used the concatenate option in summarize before! 

luciebrett
8 - Asteroid

Thanks @jdunkerley79, perfect solution - thanks!

luciebrett
8 - Asteroid

Thanks @vishwa_0308, I must have done something wrong as when I tried to do this I ended up just transposing the numbers one to each column. Thanks for your help but I have managed to solve with the other responses. Have a great day!

Labels