merge rows

[#1]

I have the below table

Name	3-support	onemore
a	table2-support3	er
n	e	rt
c	y	ty
d		no
d	oh

I want to merge the rows based on the Name for e.g.

The last 2 rows should be merged in one row as

d        oh      no

Can someone please help understand how I can accomplish this? I tried doing a self join but that didn't get me the results I want. Thanks in advance for your help.

Thanks,
Samira

[Anonymous]

based on your example, (assuming there is value only in either of 3-support/onemore fields )

1. first load distinct Name in a Table1 from from your table

2. left join Table 1, load Name, 3-support from your table where len(trim(3-support))>=1

2. left join Table 1, load Name, onemore from your table where len(trim(onemore))>=1

[Anonymous]

Please use distinct on all loads

[maxgro · Accepted solution]

try group by name

source:

load * inline [

Name, 3-support, onemore

a, table2-support3, er

n, e, rt

c, y, ty

d,,no

d, oh,

];

final:

NoConcatenate load

Name, MaxString([3-support]) as [3-support], MaxString(onemore) as onemore

Resident source

group by Name;

DROP Table source;

[#5]

Thank you for your response. This is what I tried:

join1:

LOAD DISTINCT Name,

     [3-support],

     onemore

FROM

[..\Temp\joinTest2.xlsx]

(ooxml, embedded labels, table is Sheet1);

left join(join1)

LOAD DISTINCT Name,

     [3-support]

FROM

[..\Temp\joinTest2.xlsx]

(ooxml, embedded labels, table is Sheet1)where len(trim([3-support]))>=1;

left join (join1)

LOAD DISTINCT Name,

     onemore

FROM

[..\Temp\joinTest2.xlsx]

(ooxml, embedded labels, table is Sheet1)where len(trim(onemore))>=1;

So if I pull the rows into a table box, should I now see only 1 row replacing the 2 rows?

If so then I don't see that happening. Trying to figure out what I am missing.  Your guidance is much appreciated.

Thanks,

Samira

[#6]

Thanks Massimo.

That worked!

[maxgro]

please mark the helpful and correct answers (to close the discussion), thanks

[#8]

Surely will. I am trying to apply the same solution to a larger data set  of approx 60K rows that I need to create the unique rows for and am running into errors there. Hence the delay in marking the answer as correct.

[#9]

I have a further question:

For my actual datasheet, I am doing a if(Match) during the load to load entries from one column into different new columns based on a condition. For e.g.:

if(Match([role],'First Call Support Contact'),[full name]) as [First Call Support Contact Full Name],

     if(Match([role],'First Call Support Contact'),eMail) as [First Call Support Contact Email]

so if I drop the source table I lose that data. How can I address that issue?

Thanks,

Samira

[maxgro]

could you open a new discussion with some data?

I don't understand your last question.

maybe

.....

if(role = 'First Call Support Contact', [full name]) as [First Call Support Contact Full Name],

.....