Data Preperation : Multi-column handling

[harsha384]

Hi All,

I have a query below :

Input:

Col A	Col B
11	Alan
12	Joe
13	Paul
13	Nick
13	Colan
14	Mike
14	Chris
15	Sam
16	Lee

Output :

Col A	Col B
11	Alan
12	Joe
13	Paul+Nick+Colan
14	Mike+Chris
15	Sam
16	Lee

when i tried using multi-row formula to compare and create a new field, I am getting a default value as 0.

Eg: IF [Col A]=[Row-1:Col A] THEN [Col B]=[Col B]+[Row+1:Col B] ELSE [Col B] ENDIF

Guide me how to work through it.

Thanks

Harsh

[DataBlender · Accepted solution]

Hi @harsha384

A quicker way would be to use the Summarize tool : Group by Col A, then Concat Col B. At the bottom of the configuration window you will be able to define the separator as '+' to get the result you gave.

[ShaanM]

Hi

You could solve this by using the cross tab and transpose tools

I have attached a solution

Shaan

[harsha384]

Hey thanks @Asteriod. I absolutely forgot about summarzie function.Works great!

Thanks